Posts Tagged ‘SQL Server 2005’

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.


I just found a help script that I thought I would post about. There is a blog located at on April 17th the owner put up a script that you can place in a stored procedure and use it to validate your backs ups.

One of the concerns that I have that I speak a lot about is how to make sure that you daily checklist items are being completed. It’s something that I have spoken about at, a couple of times. The short of it is that in our day to day life as a DBA is easy to forget or to set aside things that we should check everyday. Let me give you a great example:

At one point in my career I worked for a company that will remain unnamed. And we were using a 3rd party backup software. This software was designed for when you execute a backup you call it via extended stored procedures. When we set this up as a job we found something really interesting. The fact that the job called the stored procedure and the stored procedure would return a message would be considered a good execution. So unless we had the job to notify us of competed jobs, we would not see a failure when the third party product would return a message of backup failed. The job saw a return message so it considered it good to go. This blog entry that I linked to will allow you to do a restore with verify only and log the results in a table. The table code is included in the blog. This way you can take a look at the single table and see if the backups were good or bad. If you want to be really on top of the ball I imagine that you can either set a trigger or a job that will check for anything that has failed in it and send a notification, send a message to your MOM server is you are using one or any number of other monitoring tools.

Thanks for the code post Crissie.

A couple of guys that use to work with me a long time ago came out with a new book.  I received my book yesterday in the mail and plan on reading it so that I can give it a review.  Kudos to Eric Johnson and Joshua Jones, two very sharp DBA’s that it would be a pleasure to work with again.  I look forward to seeing what they produce next. 

Joshua Jones is one of the speakers at the Fall SSWUG V-Conference, and was a highly rated speaker for the Summer conference that just happened.

And Even though Eric and I are both under 40 years old I can say that I have known Eric for almost 20 years.

The new books name is “A Developer’s Guide to Data Modeling for SQL Server”  From what I have looked at it so far it was written for 2005 and 2008 versions of SQL Server, but if I know Eric and Josh a lot of this information will apply to SQL Server 2000.  Good luck on the book guys.

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. 

For years I have been working in the Corporate Work Place, I think going on 15 now. The biggest challenge that I have faced over the years is how do I manage 200 SQL Servers? Microsoft has provided me tools along the way that include SQL H2, that I used, and Reporting Services that helps but out of the box they both require set up, and some custom code that will gather information about your servers. For anyone that has heard me speak, I talk a lot about Management and how to we get a grasp on managing servers. One well attended presentation has been SQL Server Checklists, what I should do on a day to day basis. I have wrestled with myself for years about how to do it.

  • Do I do it manually the brute force method, to time consuming and to easy to forget or put aside doing checklists when I have a server down.
  • Do I do it with SQL H2 – I could, but the process to set this up can sometime be a bit tedious when looking for performance counters, and then I am left with writing custom reports for me. Another issue that I found with SQL H2 was my service would stop sometimes and if my staff did not catch it I was left without information for days.
  • Do I use Reporting Services – Well that was the direction that I went. And it is a good solution. It requires some coding and some work each time a server is taken off line, or a new one brought on line.

I have used all 3 methods in my management style, the one I have liked the best is Reporting Services, well that was until now. SQL Server 2008 gives me the ability to group my servers and to manage them together. I can run queries on all the servers at the same time along with dumping the results to a single point. From this single point I can create new reports, or run existing ones with a few changes.

So when people as me why I want to Upgrade to SQL Server 2008, I tell them. My life as a DBA is going to be much easier to find problems. It may still take time to get to the problem and fix it, but the big deal is that I can do this now with a lot less code in my trouble shooting process. In other environments I have been in I would hear that an order had not processed. Once I heard that I would have to trace down what server a job may have failed on. But it may not have been a job, it may have been that the order status was not set to process. Then I had to check inventory, and billing, and shipping, and so on. In 2008 I can run a query that will tell me all the failed jobs, I can run a query that will tell me all the order status and then I can put this in a table. I can create a report that will tell me what looks good and what does not.

SQL Server 2008 will make my life easier. It will give me the tools that I need to manage hundreds of databases across hundreds of servers. I believe that Microsoft is trying to make it easier to do my job and I thank them for that.

Not much to say today, I am really excited about everything that is going to happened on Tuesday the 24th. I have been the Conference Chair for the SSWUG SQL Server conference and the team has put in a lot of work. Many people don’t realize how much effort goes on behind the scenes to create a conference like this.

I am truly honored to be part of this through my career of public speaking this has been the most challenging experience that I have faced to date. There is a ton of great information that I am really excited to get feedback on. It was interesting to watch MVP’s do their work, speakers come up with ways to teach subjects so that people with different learning styles could pick up what was being taught. A big and special thanks goes out to all the speakers, all the attendees and all the staff at Bits on the Wire.

It has been my pleasure to work with some of the finest people in my career. I plan to post a day by day review of the conference. If you are registered, I will see you there (well virtually of course). If you have not registered, it’s not too late. Any sessions that you may have missed will be available on demand starting Friday at noon.