SQL Server Reference Guide

If you've used very many Microsoft products then you're already familiar with Wizards. They are applications within programs that walk you through complex operations in a step-by-step, guided fashion. Most of the time, these Wizards, found in everything from Microsoft Office to Front Page is used by people who aren't very familiar with the package.

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.

And that’s an important point — 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 — Ola Hallengren’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.

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.

The Maintenance Wizard in SQL Server 2000 and Earlier

If you want to try these steps out with me, always use a test system. Do NOT attempt this on your production systems until you understand how it works!

In SQL Server 2000, you'll find these Wizards in Enterprise Manager, under the Tools item in the menu bar. Once I select Wizards... from the menu choices I’m presented with several options.

Figure 1

It might seem strange to have a system with as much power as a database that provides Wizards for its use. You expect that anyone in a large, complex package like a database system should at least know a little about how to maintain it. It's a bit like finding a "Brain Surgery for Not-So-Smart People" book in your doctor's waiting room. But that's not the case here. The Wizard is not as much doing all the work as it is helping to create a simple plan.

Most Wizards in other applications have a "closed-process" type of result. That means that once you've run the Wizard the result can't easily be tampered with. In the case of the Wizards in SQL Server the program automates the technical steps, and the results that it creates can be easily edited or configured.

One of the most useful Wizards in both versions is the Maintenance Wizard. In SQL Server 2000 the Maintenance Wizard guides you through a series of steps that requests several bits of information for a complete plan, and saves the result as a series of SQL Server 2000 jobs. The Microsoft SQL Server Agent runs these jobs based on the intervals you specify. You can come back and edit any maintenance plan once it's complete.

The simplicity of a Wizard sometimes obscures the importance of the maintenance plan it forms. Let's take a quick look at both versions and how they handle the process. I'll explain the basics in SQL Server 2000.

I’ll start the explanation in SQL Server version 2000 by calling up the Maintenance Wizard. You can see in the graphic below that I’ve opened Enterprise Manager.

Figure 2

I’ve already drilled down to the Management object, then to the Database Maintenance Plans item. After I right-click that object, I select New Maintenance Plan from the menu that appears. That brings up this panel.

Figure 3

This panel is the introduction to the Maintenance Wizard, and explains what’s going to happen with the following selections. The selected databases can be checked for integrity, then optimized for speed, then backed up.

You might notice in this panel that the Wizard indicates that I can use the Maintenance Wizard to automatically back up my transaction logs and apply them to another server. But this option is only available in the Enterprise edition of SQL Server 2000.

Once I click the Next button, I’m presented with the following screen:

Figure 4

Here I’m asked to select the databases I’d like to work with. Notice that the selections include several choices, in addition to the individual databases.

The first selection is All databases. I tend not to use this option, as I normally don’t want all the maintenance tasks starting at exactly the same second. If the databases are large, they can compete with each other for resources and form "hot spots" on the hard drive as they access the same places rapidly.

The next selection is All system databases. I normally create a separate plan, and choose this option since these databases are quite small and will finish their maintenance tasks quickly. You could choose each system database individually, but a special field in the master database table tracks meta-data about databases and indicates whether the database is used by SQL Server or is a user database. Selecting this option uses that field. Should Microsoft ever add or take away a system database (say, during an upgrade or service pack) the maintenance would automatically pick up the new database.

The next option is the opposite choice — all databases other than the system ones. For the reasons I mentioned earlier, I don’t normally choose this option.

In the screen below I’ve chosen the pubs sample database for this exercise. Clicking the Next button brings up the following screen:

Figure 5

Although the logical progression is to check for errors first, the Maintenance Wizard presents this panel first. The schedule options selected later will straighten that out. This is one of the places that causes an experienced DBA grief — if you don’t take the time to understand what is happening, you can really get into trouble.

The first choice sets the page and index defragmentation options. I’ve chosen to reorganize both the pages and indexes, and I’ve set a blanket size for the free space at the end of each page. As I mentioned in another article on Database Maintenance, this choice is highly dependent on how the data is used. This particular choice should be higher for tables that have more inserts, less for those with more reads. A safer choice for a normal user database may be to use the original amount of free space.

After this screenshot, I de-selected the option to shrink the physical files. Because the “shrink” operation causes heavy fragmentation in the indexes, and because you’ll probably use the space again anyway, shrinking a database is almost always a bad idea.

Now on to that schedule. The default schedule is to perform this operation once a week, but I usually set this option to a daily timeline, at least as a default. The schedule should be dictated by the size of the database and when your server is used the least. Keep in mind how many plans are running, and at what time they run. Even better would be to have a more granular approach, but again, the Wizard is a bit heavy-handed on that front. In any case, it’s better to perform these steps than not to, so that’s where the Wizards can help.

After I click the Change button, I see the panel shown below:

Figure 6

Here I set the schedule I want for this step. I’ll repeat this process for each of the steps the Wizard takes.

First I select the OK button on the schedule panel and then Next back at the Optimizations panel. That brings up the following screen:

Figure 7

This begins the integrity checks portion of the plan. It’s important to note that the Maintenance Wizard doesn’t do a lot of fixing; it’s more of a notification step. While you might think that you want to have the Maintenance Wizard automatically fix a problem, that's usually not a good idea. It’s much better to be notified about the kind of problem you’re facing, so you can better prepare for the fix. With a complex database, sensitive data and other objects are involved, so blind corrections aren’t really the way to go.

You’ll notice I haven’t selected the option to fix "minor" problems. There are two reasons for this. Normally, "minor" problems turn into major ones. Also, the database has to be in single-user mode to use this choice. Since my databases are 24/7, I don’t have a set time for this activity. Not only that, minor problems don’t happen that often, so I leave the options as I’ve set here, set the schedule as before, and then click Next to show this panel:

Figure 8

This panel allows me to select a database backup. If it’s a small database, I verify the back as I’ve done here, but if it’s especially large I sometimes forgo this option.

A word here about backup software. Even if I have the option to purchase an expensive solution for SQL Server backups, I normally back up the databases to disk as I’ve done here, and then pick up that "dead" file with my normal backup software. This saves me a ton of money on backup software, and provides three levels of protection. Besides the running backup, I normally have (1) the files on a RAID system, (2) the backup file on the hard drive, and (3) the tape of the "dead" files off-site. If I ever need to restore the backup, I can select the last one from the drive rather than calling my offsite vendor to get back the latest tape. Of course, if you have a central backup strategy, or limited drive space, by all means use the external software.

Once I click the Next button, I see the panel shown below:

Figure 9

There are three interesting choices here. The first choice allows me to change the directory where I store the database backup files. The second choice creates a subdirectory for each database — an especially handy choice for multiple databases in the plan. Even if I didn’t, it really helps to separate out the backup files.

The last choice is to remove backup files older than a certain number of days. I use this option for as many days as the space on the drive can afford, for all the reasons I mentioned earlier.

After making those selections, clicking the Next button brings up the following panel:

Figure 10

Here I’m given the opportunity to back up the transaction logs. However, this option only works if the database is in the Full or Bulk Logged recovery mode. This database uses the Simple model, which means I can’t back up the transaction log, so I click Next. It’s important to keep in mind that if you’re using the Wizard to back up multiple databases at one time, that you create a plan for the databases that are in “Simple” recovery (no Transaction Log Backups possible) and the “Full” recovery model (requires a Log Backup). If you don’t know what those things mean, stop now and read up on that right away. This is one topic that you can’t afford not to know about, since either setting has dramatic consequences.

Making my selections brings up this screen:

Figure 11

This panel gives me the feedback I need. You simply must review the results of these plans or you won’t know if the databases are in good shape. You can also have the results e-mailed to you, if SQL Mail is set up properly.

For the cleanup step, I also have the plan remove the reports on the same level as the old backups. I select Next to continue.

Figure 12

This panel continues the reporting, by placing the results of the plan in a table. I’ll show you a handy SQL Script later that will read this table, and let you save it to a file or even to a Web page. I’ll show you an example of that in the next article.

If you have a central server used for reporting, you can send the results there with an option on this tab as well.

After I leave this panel, the final screen shows up:

Figure 13

Here I name the plan. The plan I’ve been describing all along is actually a series of SQL Jobs. Once I complete the Maintenance Wizard, I can jump over to SQL Server Agent and then Jobs in Enterprise Manager, and see the names I choose here as part of the name of several jobs. I can even add steps to that job and make a more customized plan for the server.

That’s all there is to it. I can close the tool, and assuming that the SQL Server Agent service is running, the Maintenance Plan jobs will run.

Once the plan runs, I can open the Query Analyzer tool, set the output to text, and run the following script:

USE msdb
GO
SELECT ’Succeeded: ’ + CAST(succeeded AS VARCHAR(1))
+ CHAR(13) +
’Completed on: ’ + CAST(end_time AS VARCHAR(11))
+ CHAR(13) +
’Database Name: ’ + database_name
+ CHAR(13) +
’Activity: ’ + activity
+ CHAR(13) +
’Duration: ’ + CAST(duration as varchar(1))
+ CHAR(13) +
message + CHAR(13)
FROM sysdbmaintplan_history
WHERE DATEDIFF(day, end_time, getdate()) < 2
ORDER BY succeeded, database_nameSQL Server 2005

In the next article I’ll cover this same process for SQL Server 2005, which has been dramatically improved. It’s still a Wizard, however, so the same caveats apply for understanding what it is doing before you use it.