Posts Tagged ‘SQL Server 2008’

Maintenance in General is a necessity for SQL Server.  No different than changing the oil in your car or going to the Doctor for the annual exam.  There is going to be times when you are going to need to run maintenance on your server.  The tricky part is trying to determine when you should start the maintenance jobs before the busy time. For example, what if you need to backup your database, then re-index your database and follow it up with a consistency check.

The common approach to this problem is to simply determine how long a job executes (often determined by trial and error) and then adjust the start time of each job to give the job enough time to execute, before starting the next job.  The problem with this method is you are hoping the first job in the chain completes on time before you start the second job.  The common way to avoid this is leaving gaps so one long running job does not step on the next job.  However, there are options…

Option 1

If you are using a maintenance plan you can keep all the tasks that are schedule to run at the same time in the same sub-plan.  Sometimes this does not provide the flexibility that individuals want, but it is an effective method.

Option 2

You can create multiple steps to a single job.  If we use the example above where you want to run a backups, than re-index and then DBCC, you can create 3 different steps, this way as soon as one step completes the next step is executed. This method removes the need for guessing when one job would finish and the next job start.

Option 3

Each task could have its own job, then the last step of each job would start the next job.  This will add a lot of flexibility to your maintenance.  I like to use this in a couple different kinds of situations.

  1. If your maintenance is done by using multiple tools, for example… a Red Gate Backup, a custom re-indexing plan and a simple t-sql script to run a consistency check.
  2. If your maintenance is done across multiple servers… If you have 3 servers that all backup to the same network share, you could have one server execute at a time to not clog up the network and the storage.

Adding a step to execute the next job is pretty simple.

exec sp_start_job @job_name=N’My Job Name’


If you need to schedule this to occur across server, you can simply make the call to the other server using a linked server.

I hope this tip has helped you in one fashion or another.




Maintenance in General is a necessity for SQL Server.  No different than changing the oil in your car or going to the Doctor for the annual exam.  There is going to be times when you are going to need to run maintenance on your server.  The tricky part is trying to determine when you should start the maintenance so that it completes before the busy time.  The common approach to this problem is to simply determine how long a job executes (often determined by trial and error) and then adjust the start time to give the job enough time to execute.  There is another way…

SQL Server has a number of system stored procedures that you can use to perform tasks that you might be doing in the user interface, for example… If you want to stop a job you can open SQL Server Management Studio, navigate to the job, right click and stop the job.  Here is where the system supplied stored procedure comes into play.  What if your busy time of the day is at 6 AM, and you want to make sure that the indexing has finished by 5:00 AM so that the system is ready to take on the day.  Do you really want to wake up at 5:00 AM just to right click and stop job, in the chance that it is running?

Simply schedule a job that will execute at 5:00 AM (the time you want to make sure the maintenance job is done by), and create a step that will stop the job.

exec sp_stop_job @job_name=N’My Job Name’

Not to complex. But what if you want to add some logic to the job so that not only does it just try to stop the job it will check the job to determine if it is executing first?  And now that we are looking at some of the options there are, we should put a line of code in there that will email us whenever the maintenance job has run long and had to be stopped.

Select name

from msdb..sysjobs j

join msdb..sysjobactivity a on j.job_id = a.job_id and = ‘My Job Name’

Where start_execution_date is not null and stop_execution_date is null

If @@rowcount > 0


EXEC msdb.dbo.sp_stop_job @job_name = ‘My Job Name’

EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘MyMailProfile’, @recipients = ‘’,

@body = ‘The Indexing Rebuild Job had to be stopped due to long run time.’, @subject = ‘Index Rebuild’ ;


Else Return

I hope this tip has helped you in one fashion or another.  If you would like my list of TOP 10 TIPS FOR SQL SERVER PERFORMANCE AND RESILIENCY can be found here with Tip # 1.


Stephen Wynkoop and I have been working on a “special event” for some time now. We have been trying to figure out how we can help get the word out about SQL Server 2008. The really exciting news is that we have figured out a way to do it. Everyone on the planet can join this Green online SQL Server 2008 Community Launch Event. We are really excited about this. This event will take place on October 24th, 2008 and will not cost anyone a thing. The start time is 9 AM PST and is going to be a great way to open up the Fall Conference Season with SQL Connections, and SSWUG Conferences.

I will be speaking on some of the new features that are in SQL Server 2008 and Jason Strate will be discussing the new features in SQL Server 2008 SSIS. I don’t think this is going to be an event that you are going to want to miss. The cost for this is free, you cannot lose. This is going to give you some great information that you can pass on to others on why they would want upgrade to SQL Server 2008. In my opinion the best released version of SQL Server ever.

The day will finish with Jason and Stephen doing a candid sit down discussion, followed up by some wrap up notes. I really look forward to everyone being there.

I got some really exciting news today. I talked with Stephen Wynkoop, the founder of SSWUG and Microsoft MVP. It looks like we are going to get him to do an additional session. A review of some of the new tools that come inside SQL Server 2008, he will also be doing all the keynotes that went really well in the last VConference that were based on SQL Injection. The code samples that he showed in the keynotes were amazing. It one thing to hear about how people do it, but a completely different thing when you can see how this code damages your database so bad.

To top the day off I got to talk to Donald Farmer a BI Guru that really had a lot of great things to say. Many times you meet these people and when you talk to them about problems that you see on a day to day basis you worry that you are the only one that is going to see that problem. Let me give you an example. I ran into something funny with SQL Server 2008 last week. My system computer was showing that I had x amount of memory yet the SQL Server that I thought was installed locally was showing 4 times that. I thought wow, maybe something is really wrong here. I jumped up on the newsgroups on Microsoft and posted my question. I was really surprised when I got a lighting fast response from a friend of mine Kalen Delaney author of the Inside SQL Server books. After a couple of posts that went back and forth I figured out that it was completely on my side and that I was looking at another server via my client tools. Thanks to Kalen for the help. But did I feel stupid. I guess it just reminds me to stick to the details.

Well good day for now. Chances are you are going to see an exciting announcement from me some time tomorrow. There is some things that I have been working with and I think it will be announced in the next 24 hours.

I have loaded SQL Server 2008 CTP on my laptop a few times now. I used it for presentations during the June SSWUG SQL Conference. I even put the RTM on my Vista machine and had not a single issue. Yesterday around 4:00 PM I ran started to put the same install on my laptop to prepare for my sessions for the Oct SSWUG Conference and I ran into an issue. For some reason I was getting a .Net Framework 2.0a error. After reading a few blogs and MSDN articles I was not better off. I tried to repair my .NET Framework. Heck I even tried to install sp1 to it just to see if that would fly and I was stuck.

You know my last post I spent an hour writing about how we tell people that it is ok to reboot their machine. Well this is a lot along the same lines. After working on it until 2AM this morning I went to bed. I repositioned my mind in a happy place this morning and decided that it was time to build from the ground up. I removed all the .Net Framework installs and reboot my system then proceeded with this process.

  • Installed .Net Framework 2.0
  • Reboot
  • Installed .Net Framework 2.0 sp1
  • Reboot
  • Installed .Net Framework 3.0
  • Reboot
  • Installed .Net Framework 3.5
  • Reboot
  • The fired away with my SQL Server 2008 Standard Edition.

So hear I am 24 hours later and I am up and running on SQL Server 2008 on my laptop running Windows XP Pro. I hope you want to read a lot more about SQL Server 2008 because I am starting to write my presentations now.

It’s been a busy last few days. We started it off with the release of SQL Server 2008. The excitement that comes from a major release like this is almost too much to contain. I know I am ready to start installing it and checking out how all the new features made it into the final release.
Well from what I am hearing you may want to wait a bit before you go and install it. From my understanding the best way to increase your chances of installing SQL Server 2008 is by starting from scratch on a new OS install. I have heard that there are issues with software that exists on the server before SQL Server 2008 has been installed and it is causing many problems.
If you have been able to get SQL Server 2008 installed with some success I would love to see a comment or two that shares with me the configuration that you had prior to the install.

I found it found something interesting today as I was helping a client work through an issue with SQL Server 2005. See the client of mine is working on moving a bunch of code to Stored Procedures. The client is currently on SQL Server 2005 and I was showing them a couple of samples of code by using SQL Server 2008 because that is what I have installed on my machine. What I found interesting was as I was going though this we ran across the function in SQL Server 2008 that helps you complete your code. I believe the official name of this is called IntelliSense. I was explaining what it was and it dawned on me that they might not know that the Dynamic Help feature was there.

This brings me to the though process that there are still a lot of hidden gems inside SQL Server 2005 that a lot of people are not using or just are not aware that they are there. When 2008 is released in a couple of months we are going to be in the same situation.

As far as IntelliSense is concerned, if you have not seen it yet, check out the latest RC to see how it works. It may take a little getting use to for us DBA’s but for those people who have been using .net for a while will pick it up right away. Some of the neat things that it can do for you is underline what it believes to be bad syntax with the red you spelled something wrong line like in Word. However, I found that so far its not working with Inserts and Updates as well as it does the other SQL Statements. The best part about this is that it is installed on default. It’s not a feature that you have to go in and tune or set it up.

An old friend of mine today asked me a question that I thought I should go ahead and post.  The question was; how do you run a join across databases.  Do let’s say for example you would like results to be a combination of sales from the Employee database and the Sales database (so you want to know who the best sales person is), but they are in different databases.

Good question but depends on the version of SQL Server.  All you have to do is hit it by the fully qualified name.   So for an example.  If you wanted to do this and both databases (Employee and Test1) are on the same server then you would do a join like this.

Select (Columns)

From Employees.dbo.Employees e

                Join Sale.dbo.sales s on

So really the answer is no matter what the version you have to use a fully qualified name.  Here is the name pattern <LinkedServerName>.<DatabaseName>.<Owner>.<TABLE> now this is for SQL Server 2000 and older. 

 If you are working with SQL Server 2005 or newer then it changes to <LinkedServerName>.<DatabaseName>.<Scheme>.<TABLE>

There really is not a big difference here.  Just remember if you normally when you write a SQL Statement the defaults of the database you are in and DBO as the owner are used.  If you use one to the left of the Table name such as database name like this (Employees..Employees) I think it looks and reads the best if you still include the owner or the scheme. 

I was looking at a friend’s IM message today and it said “don’t forget to sanitize your data inputs”. I got to thinking about how I will put up stupid NASCAR sayings just to poke fun at another friend of mine.  The more that I sat down and thought about it I realized the importance of the statement that my friend was making.  So the question is to why should I sanitize my data inputs, and what does that really mean?

When you create a stored Procedure the format you use will look like this:

Create Procedure ProcName

@Var1 (varchar)


Select Name from PhoneBook

Where name like @var1


If you are new to stored procedures the data input here is the “@Var1”, this is the place where the users request is going to be sent to the database.  For example let’s say that this stored procedure is called from a web page.  A user could select to see if there was someone in the database named “Chris Shaw” or “Bob Smith”.  Why would we want to sanitize it? Some people may think that we would be limiting the search results by doing this.

What is sanitizing? Well its making sure that any value that is inserted into that @Var1 is data that we expect it to look like.  An Example of this would be to make sure that there is not a ‘;’ in the var.  I am not sure I have ever seen anyone with a semi-colon in the name.  These non useful characters need to be pulled out and/or a message that this input did not produce any results.

Well the end results are this:

  • Keep your data clean – The example I gave you was a search and not in insert, but if it were an insert the end result is that you don’t want data in your database that is not sharp, accurate and clean.
  • SQL Injection – Many developers in the past and still today will put the SQL code into the web page itself. This has caused a lot of issues but ads a lot of flexibility, the end result is no matter where the input is; if you are not checking to make sure that data is clean you are at risk.

So a few years back there was this whole big issue with public companies not reporting the financials accurately. I am sure many of us remember this. There were a number of companies involved and well the end result was that these companies made it look like they were doing better then what they were. When the truth came to light the stock plummeted, and a lot of people who were invested ended up losing a lot of money, in some cases life savings.

Because of this a new Act was passed, more or less (and I am not a lawyer) this Act makes the CEO and the CFO of public companies responsible personally if there is an issue with the books not being done right. The boards of these companies started to hire firms to come in and do audits on everything that touched the revenue stream. The called it the Sarbanes Oxley Act or (SOX). And the CEO’s and CFO’s take a good look at this.

This is where this gets very fuzzy, I have been at some places where anything can touch the revenue stream. I have been at a company where only select applications touched the revenue stream. Yet they tested the security at the building.

More to the point though, SOX has been a big unknown cloud. I have been through 3 or 4 SOX audits and every one was different. I have even been to places where they have pre-audit companies come in to help with the Audit and they ask completely different questions.

Where I am going with this? Well the new Audit Features inside SQL Server 2008 are going to allow you to automate most everything that I have been asked about before. Questions like; when did all the accounts last have a password change. Or they can validate that the database has good backups or even when the last restore was done. This new Audit feature will even log when it has been changed.

The point that I am trying to make here is not only is Microsoft trying to make our lives easier by helping use with new features like compression or policy management, but they are also looking at what the industry needs at the time. We are being asked to provide audit info and SQL Server 2008 will help DBA’s like me get there.