Hey,
Sorry for the sporadic posts, planning a wedding and blogging do not necessarily go hand in hand!
Anyway, moving on, I’m putting together a presentation for the Reluctant DBA, and part of it will be on the Maintenance plan wizard, and how it is the (slightly blunt) swiss army knife for the DBA with time in short supply. So I thought it might be good to do a series of blogs on the Maintenance Plan Wizard, what each task does, why you should be running the tasks, and a set of guidelines on when/how to run them.
The two pictures below show how to open the wizard, and what you’ll see when you run through it.
 |
| Where to find the Wizard |
 |
| Maintenance Plan Options |
So for today I’ll cover Check Database Integrity and Shrink Database.
Check Database Integrity
This is an important one to run, and as the name suggests checks that everything is ok with your database. Think of it as chkdsk for your database. For a full list of what this job does have a read of this article on technet. It essentially scans the objects and data in your database, to make sure that everything your database contains is stored correctly and accurately. As the check generates a snapshot of your database (From SQL 2005 on) and scans through that, it’s a lot less intrusive than on SQL 2000, but it still places quite a bit of load on your server as it scans through the Snapshot. Important to run often, but try to schedule it in quiet times. All information/relevant output will appear in your SQL Server log.
Shrink Database
Again, as the name suggest, this plan shrinks the size of your database on the disk. If you want to have a look at the process behind it, have a look here. I am not a fan of this one, particularly for databases which are required to perform quickly. Basically it shrinks your file down by freeing up whatever free space is available in the data or log file. It tries to move all of your data to the beginning of the log file, and cut off whatever isn’t needed, giving you that space back. Sounds good so far right? And for a small database which only gets accessed once a year, maybe that is good. It all depends on your situation. The pit falls of shrinking your data files like this, to name but a few are :
- Moving data pages around in your database file fragments your database
- If the files got that big before, they may again, and when it autogrows that is a time consuming process which slows operations while it grabs the space.
- Shrinking and growing your data files fragments the files themselves. They may not be able to grab a contiguous block on the drive as something else may have landed on the drive since, and now your log file isn’t stored contiguously on your drive, increasing the I/O required to read across the file
- While your .LDF is a single file, it contains Virtual Log Files for SQL server to operate from. This allows sections of the LDF to be cleared re-used when the data is written to disk, rather than having to consistently grow like your data file does. Every time you autogrow it has to create new Virtual Log files, again potentially degrading your performance due to the amount of VLF’s which require reading and writing.