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.