SQL Server Reference Guide

I’ve divided this article series into two parts. The first deals with SQL Server version 2000 and lower, if you still have that around. The second deals with SQL Server 2005 and later — but with a caveat. A fairly significant change in Service Pack 2 for SQL Server 2005 allowed for separate schedules for each task, and that’s critical to using the Wizard properly. If you are on SQL Server 2005 with SP1 or earlier, you’ll need to keep that in mind. Anything later than that has the fix.

I’ll cover some of the same information in both articles, just in case you’ve arrived here by way of a link.

If you've been assigned to work on SQL Server and you're not an expert, or perhaps you've been asked to step in to a DBA role that's unfamiliar to you, you'll find several Wizards in SQL Server that will guide you through many tasks. Used properly, these Wizards can be very useful — but you need to understand what they are doing for you, and when and where they are appropriate to use.

This is the second in a series of articles dealing with Maintenance Wizards in SQL Server. In the first one I covered SQL Server 2000 and earlier. I’m repeating a lot of the same general information in both tutorials, so they’ll look alike, but I’ll be specific in this one to SQL Server 2005 with Service Pack 2 and later. A fairly significant change in Service Pack 2 for SQL Server 2005 allowed for separate schedules for each task, and that’s critical to using the Wizard properly. If you are on SQL Server 2005 with SP1 or earlier, you’ll need to keep that in mind. Anything later than that has the fix.

You should only use the Wizards when you have a small system with no maintenance on it and you’re not sure how all that works. I advocate that you use a Maintenance Wizard to get you up and running quickly, and then learn a more “manual” approach to replace the Wizard steps.

You should NOT use a Maintenance Wizard blindly, or with a large, complex system. In those cases, you can actually cause more issues than you fix. If you have one of these situations, educate yourself on creating a comprehensive maintenance plan, or even better, hire a qualified consultant to assist you. You can find those at many locations, including Microsoft, third parties and independents — just make sure you check references carefully in all cases. This is a critical step, and eventually even if you get help you’ll own the process. You want to ensure that you can handle things when the consultant is gone.

There are three or four main areas in maintenance — some of which can be done in an automated fashion using a Wizard and others which can’t. I should also note here that there are automated methods to do more of these — Olla Halengren’s scripts are some of the best in the world. I’m limiting my discussion here to the Wizards in SQL Server, however. Here’s a starter list of maintenance areas, and where the Wizards can be useful or not:

• Error and Event Log Review - This one can’t be done by a wizard.
• Database Consistency Checks - Can be done by a Wizard, but you still have to review the output.
• Index Maintenance — Can be done with a Wizard, but they are a bit “monolithic” — meaning that you can’t really set a level of fragmentation to use. It either does the rebuild or reorganization, or it doesn’t. If you don’t know what any of that means, read on.

Database Backups — Can be done by a Wizard, but once again the choices are limited. You can’t get a comprehensive database backup plan with a Wizard, but it can handle the basics.

You can also check out my Maintenance Checklists, in addition to others from folks like Brad McGehee.

The Maintenance Wizard in SQL Server 2005 (SP2) and Later

There’s a big difference between the Maintenance Plans in SQL Server 2005 and later, and the Wizard steps in SQL Server 2000 and earlier. The later versions store the Maintenance Plans in SQL Server Integration Services (SSIS), a feature introduced in SQL Server 2005.

The primary issue with Maintenance Plan Wizards in SQL Server 2005 and later is the same one with the earlier versions — if you just accept all choices and all defaults, you’ll end up in a bad situation. If, however, you are a smaller shop and you read and understand the choices you make, the Maintenance Plan Wizards can be a good option for you.

Opening SSQL Server Management Studio (SSMS), I’ll walk through creating a simple maintenance plan and explain the options. It’s important to understand that to create a more comprehensive plan, you would run through the Wizard multiple times based on the needs of each database. In this example, I’ll set up a Wizard to do all of the maintenance on the system databases — the ones that come from Microsoft, that essentially form the heart of the data used to run SQL Server. You can follow along on your test system, and in fact leave this plan in place there.

I’ve opened SSMS and navigated to the Management node in Object Explorer (OE) and right-clicked Maintenance Plans item.

Figure 1

I have two options — one to create a Maintenance Plan, which drops me into a blank canvas with actions and connections I can work with. This is sort of a cross between a Wizard approach, since it’s graphical, and a more scripted approach, since you have to build it yourself and understand what each object does.

Remember — I advocate that you create multiple Maintenance Plans, if you plan to use the Wizards to create your plans. I tend to create a plan for things that “go together” — in this case, I’ll keep the systems databases together, since on my system they have the same settings, backup requirements and so on.

In this example, I’ll pick the Maintenance Plan Wizard, which will also build a plan (technically an SSIS Package) but will guide me through the process. That selection brings up the following screen.

Figure 2

This is just an introductory screen, and you can check the box at the far left to skip it the next time. Clicking next here brings up the next screen.

Figure 3

Here I name the plan. I’ve actually removed the spaces in the name. The reason is that this plan actually has an entry in a table that later on I might work with, for reporting and so on. It’s just easier for me to work with values that don’t have the spaces, so that’s what I’m doing here. You can certainly include the spaces if you like. You’re also welcome to skip the Description field, but I normally put at least who created the plan and why.

The most important box on this screen was introduced in SQL Server 2005, in Service Pack 2. It’s marked “Separate schedules for each task”. That selection allows you to schedule each part of the script separately, which is a huge improvement over earlier releases. This allows a much better level of control for each step.

After those selections, I click Next to see the next screen.

Figure 4

Here I set the tasks I want to perform in this plan. There are a couple of things I need to point out on this screen, since this is where the Wizard can go wrong — if you just take all choices.

First, I do in fact want to check the physical integrity of the system. In fact, this is one of the main areas required in a maintenance plan anyway, the DBCC checks on the databases.

The next option — Shrink Database — should never be run. It sounds good, and it sounds like the right thing to do, but it actually isn’t. Shrinking a database causes fragmentation in the indexes, among other issues, so it just shouldn’t be done.

There next two operations are grouped together and that could also be a problem. You need to perform Index maintenance (you can read more on that here) but you only need one or the other of these two steps. The first, “Reorganize,” is a lighter level of defragmentation on the Indexes, but takes fewer resources and runs faster. Normally, you’ll do this when the fragmentation is below 30 or 40 percent.

The second option, “Rebuild,” completely re-writes the Index, taking more time and resources. This is normally done when the fragmentation is at 40 or 50 percent. The point is, you shouldn’t do both. If you’re unsure, read up on the options you have and choose one or the other. In this case, I tend to Reorganize most of the time, and then from time to time I measure the fragmentation and then follow the steps to Rebuild the Indexes.

The next choice is to Update the Statistics. Statistics are actually another kind of Index, but in my case I have settings on the database options to automatically create the Statistics and to keep them up to date. So this step isn’t necessary — ergo I de-select it here.

I select both the “Cleanup” tasks, that take care of removing the older reports of the activities, and the older backups. The choices for that — along with all of the other selections follow up.

Below that step is a selection to run an Agent Job. If you don’t know what that is, or you don’t have an Agent Job you want to run, then de-select it.

The next three options deal with the database backups, which is one of the most important steps in maintenance. But once again, you need to understand your choices.

At the very least, you need to take a Full database backup. You need to do that as often as the changes dictate, and a Full backup is only part of what you need to do. If you’re setting up a more comprehensive Maintenance Plan and this is the second or subsequent one you’re creating, then you might select the “Differential” backup option — but only if you have done a Full backup already. A Differential Backup backs up all the changes since the last Full Backup.

The next option is a Transaction Log backup. That’s a bit more complex than I want to cover here, so if you’re unfamiliar with the Transaction Log and Recover Levels, check out this article.

A Transaction Log Backup is only possible if the Database Recovery Level is Full or Bulk Logged — and my system databases aren’t. Not only that, you wouldn’t take a Full Backup and a Log Backup at the same time — that would serve no purpose.

The upshot is that you would not want to select all three backups in the same plan. This is another reason that some folks consider the Maintenance Wizard to be less useful than it could be — it seems to be a tool designed to help a beginner, but it doesn’t explain some of these more complex operations.

As I mentioned, I do select the cleanup tasks, and I’ll show you what those do in a moment. From here, I select Next to bring up the next screen.

Figure 5

This screen sets up the order of the tasks. Once again, it would be useful if Microsoft explained the ramifications of each choice, but they don’t.

The general pattern I follow is to check the database for errors first, then fix the Indexes, then do the backups. Cleanups should happen after their particular step runs, of course. You’ll notice, however, that in my screen I can’t change the order. That’s because I selected each step to have a different schedule, so in effect I’m the one that sets the order, in the step itself a little later. Again, this is the kind of thing you should see explained. From here I click Next to bring up this screen.

Figure 6

In the top part of this Screen you’ll notice that I’m working with the step that checks the database integrity, one of the key parts of the process. The first thing I do is select the database or databases I want this particular plan to work with. I don’t recommend that you pick “all databases” or even “all user databases.” Here’s why.

Selecting either one of these choices is interesting because it will automatically track all databases on the system — even new ones you create — and perform that step against it. While that sounds good, it can be counter-productive, since the step will start on all of them at once, which can have adverse effects on the system. And of course those databases might have different settings or options, such as the Recovery Level that can affect the step. For instance, you can’t back up the Transaction Log on a database with a Simple Recovery Model, so you couldn’t set the same kinds of backups for all database. Since these choices will pick up databases you don’t even know about yet, it’s dangerous to blindly make that choice.

In the case of the system databases, however, I do select all of them. Making the choice I have here ensures that even if Microsoft adds another system database in the future, this plan will work. And most system databases have the same settings — although you should make sure that’s true before you make this choice.

On this screen and the ones that follow, you’ll notice I check the box to ignore databases that aren’t online, since that will cause the plan to fail out if I don’t. If they are offline, that’s for a reason, so I don’t want to try and maintain them in that state.

After I press OK on the database selection, I’m returned to the main part of this panel, where I can set the schedule for this task. In effect, this becomes the “order” I mentioned a minute ago. I’m not showing that screen here, but simply clicking the “Change” button shows a pretty easy-to-understand screen that has what you need on it. Even though I’m not showing that screen, it’s the mix of schedules and separate Maintenance Plans for each database or situation that makes the Wizard useful — not in accepting every choice without understanding them.

You may notice that in my screens I’m not selecting a schedule at all. At the end of this process, SQL Server makes two general items — an SSIS Package with these steps, and a SQL Server Agent Job to run them on a schedule. I pick “Manual” here and then work with Transact-SQL statements to run the Agent Jobs individually, when they are needed. That’s how I get around the “run everything at once” problem. I let the graphical tool handle the heavy-lifting, and I just run them when I want. If you’re new to SQL Server, it’s probably easier to work with the schedules, however.

After pressing Next, I get this screen.

Figure 7

Here I’m selecting the Index maintenance, and it has the same choices as before — the database(s) I want to work with and the schedule. There is a “compact large objects” setting here that I also choose. I click Next to move to the next step.

Figure 8

In this screen I’ll select the cleanup for the history of all these steps. The history is stored in multiple places, but this is really dealing with the text reports that the system makes that I need to review each day. Since I do review them every day, I remove anything older than a day. I only do that because I have a separate system backup in Windows Server that backs up the hard drive to tape — meaning I can get older reports whenever I want. If you don’t have that process, make sure you set this interval long enough to where you can review the reports. I’ll show you where those land in a moment. From here I press Next to bring up this screen.

Figure 9

This screen sets the Full Backups for the databases I chose. I’m sending mine to disk, and I like to create a directory for each of those backups. You can also verify the backups, that is, check to make sure they are physically sound, but it does add time and resources to the process. I leave the rest of the settings at the defaults and press Next to continue.

Figure 10

This is a screen you need to pay special attention to. What it does is to delete the older backups. Once again, I’m backing up to disk, and then the Windows backup picks up those files each night. If you do this, make sure that the Windows tape backup runs before this task!

Also, ensure that the directory is what you want. With my choice, the cleanup task will search the entire directory (and below) and remove older files. If I’m mixing plans that have different backup types and dates, I could (and have) remove databases that I only back up once a week. Sure, at some point they end up on tape, but it’s pretty scary to see all those subdirectories empty out if that wasn’t what you’ve planned. So make sure you check the schedule here, the File age, and the sub-directory. Very important.

From here I press Next to move to this screen.

Figure 11

This sets those report files I’ve mentioned. In this screen I have them going to the default locations, which might not be easy to find or accessible to everyone. If you do change it, perhaps to a share or some other common location, ensure that the Windows Service of the SQL Server Agent and SQL Server has an account that can access those locations or your plan will fail.

If you’ve set up a SQL Server Operator with the Agent system, you can have the system mail you the reports in addition or instead of writing them to a file. If you haven’t set that up, the system will fail the step. Even if you have it mail you the results, I recommend that you make a report as well, just in case.

Pressing Next brings up this screen.

Figure 12

In this final screen you can check to make sure the Wizard will create what you want. You have a “Back” button here to make changes if not. If you do select to go “Back” in this or any other screen, make sure that you check the selections, since some of them are not remembered if you go back to a different screen. Some choices affect others.

Pressing “Finish” here starts the step creation process,

Figure 13

Once the process completes, you can open the Maintenance Plan and review it in the Package format in the Object Browser of SSMS right where you started the Wizard.

Figure 14

As I mentioned, what SQL Server did was not only create an SSIS package (shown above) which are the work to do — it also creates SQL Server Agent Jobs as you can see here.

Figure 15

Using the T-SQL to start the jobs allows me a great deal of flexibility. I can also just change the schedule in the Wizard, or even change the schedule of each step.

So the Wizard isn’t evil. It’s just that, like all Wizards, you should understand what he plans to do that’s important.