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.

Just a short note today, we have one week left before the SSWUG SQL Server Virtual Conference. We are really getting excited about the 11 speakers and the 30 sessions that we have this time. I have some 10% off discount codes if you want them feel free to e-mail me at Chris[AT] The price is only $100.00 and I know when you compare this to going to a live conference the savings is huge. I also know that when we did a survey last Feb. that 86% of the over 800 attendees said they learned something they could use that week at work. Back to work with me. Just thought I would drop a little note about the conference and what I am doing.

It was Nov 2005, doesn’t seam that long ago to me granted it was the end of 2005 and I was going to the “Rock the Launch” to help as a volunteer with my local user group. The big discussion topics that we were hearing from the audience focused around the release cycle. Questions came up about “why did Microsoft wait 5 years to release the new product” to “Will it be another 5 years before we see the next version?” There was a strong group of people that were discussing that this was just too long between releases. The thing that occurred to me as our local Developer Evangelist was speaking was I know there are companies that are still running SQL Server 6.5, they could not upgrade to SQL Server 7.0 or even SQL Server 2000.

Now we sit post SQL Server 2008 Launch I reflect back to the places that I know were on SQL Server 6.5 less then 18 months later and we now have a new version of SQL Server. From my looks into the new version I am excited to see the new features but I can not help looking back and thinking 18 months ago some DBA’s were trying to get away from SQL Server 6.5. By this time next year we are going to be supporting at least 3 versions of SQL Server in Production (2000, 2005, and 2008), are we ready for the new challenges or are these challenges really that new? We know that we should try to keep our systems up and running on the most current release, but are there times when we should stay on an older version.

As a speaker, author I try to stay to the most recent version when I work teaching a session on SQL Server. The problem that I think we are going to start seeing is there are new people coming into the field everyday. There is plenty of reading material for them to learn from about older versions if they are required to support it, but what about peer support? As a DBA I focus on keeping the pressure on 3rd party vendors to make sure they support the newest version of SQL Server and that they have plans on how to support the next version. Should we as DBA’s be prepared for the rest of our careers to support 3 or even 4 versions of the same product because vendors are not upgrading software? What do you think is the ideal release cycle?

I just finished watching a session on some of the feature sets that were announced in SQL Server 2005. I look at the SQL Server Profiler, Database Snapshots, CLR, Database Mirroring and many others. I then reflect too many of the clients that I work with or have worked with that are running SQL Server 2005 and what features that they are using. SQL Server 2005 has a lot to offer, but from what I see most shops are not using many if not most of these features.

This makes me wonder about what is going to happen when SQL Server 2008 is release, now we have Policy Based Management, a Resource Governor, and a new Reports Builder tool. Are we as DBA’s going to still see databases that were architected in SQL Server 2000 being pushed to SQL Server 2008? There is some benefit to this. One of these benefits is that they get to use the new engine that sits behind SQL Server another is that your version will stay in support with Microsoft. However, I am seeing many places where database mirroring may fit into a company really well, but since it will take to much time to redesign the front end application or a re-direction of the budget funds would cause too much pain they choose to stay with the existing plan that is in place.

A perfect example of what I am talking about it a company that I use to do some work with wanted to have a failover site in a different city (2000 Miles away to be exact). It was a fairly simple request and should not have been that hard to architect around. Off the top of my head I could think of a couple of really good ways that this could be done:

1. Transaction Log shipping – In a simple explanation it moves a copy of the backup of the database automatically over to the fail over server, restores it, and then does the same with the logs. (SQL Server 2000 has this embedded)

2. Database Mirroring – Allows for a database to have a mirrored copy on another server, in another city. The neat item with this is there is a witness server that will keep track of who is online and who is not. The witness will control the fail over. (SQL Server 2005)

However, what the company did was write their own version of transaction log shipping. This process included a piece of software that was written in house to copy the files from one point to the other. The copy software did a great job, but my issue with this is that we were re-creating the wheel. We were putting software in place that was already there in the feature set of SQL Server 2000, eight year old technology at this point. This does not even include what could have been done using SQL Server 2005.

My cry to you is this, when you start looking at upgrading your version of SQL Server to either 2008 or whatever version you are considering. Please take time to learn the new features, I believe in the long run you will save yourself time, the company money, and in the long run you will be using your tool to the fullest. Tom Roush does a great job a talking about databases for the common person. Think of it this way, why buy a multipurpose tool like a Leatherman if all you need is to twist in one screw. In a Leatherman there are many tools but if you only need to use it to twist a screw, shouldn’t you just use a screw driver?