SQL Maintenance Plans

I imagine at least half of you will disagree with me, but I find the Database Maintenance Plans to be a worthwhile tool. I guess the biggest drawback (other than a bug or two early on) is that it's a black box. I think we'd all be happier if we could see what it's doing! Still, one of the nice things about the black box approach is that if you're using a maintenance plan, you'll always get the same results.

Could you accomplish the same thing without the wizard approach? Sure! Let's take a look at everything it does:

• Log shipping
• Rebuild indexes and optionally change the amount of free space per page
• Update statistics
• Remove unused space
• Check database integrity and optionally repair any errors found
• Run a full backup and verify
• Optionally put the backup files in separate folders
• Remove backup files older than a given amount of time
• Back up the transaction log and verify
• Write a report of everything accomplished and optionally email to an operator
• Limit the number of rows of history stored
• Apply any or all of these to all the system databases, all the user databases, both, or select a combination manually.
• Set a separate schedule for just about each one of these choices
• Allow you to quickly change of the above options - ideally make it so that a less trained person could change the options easily

Doable, but a fair amount of code to get it all right and working solid. We can shorten the time if we skip log shipping (not as commonly used) and pull some handy scripts out of the library. Now be fair when you answer this question - would something we built be "better" than the wizard, or are we just happier because we can see the code? Or do you find that the plans just don't go far enough? There are a couple things I think should be included:

• Ability to indicate which databases the plan applies to by entering a select statement
• I'm probably the only one with this problem, but I'd like to know to run the log reader during maintenance tasks if the database is replicated

Of course if I could have it all, I'd include a few more things:

• Logging of space used by database and by table
• Option to do differential backups
• Option to do log backups based on demand (x transactions or x MB in log size)
• Performance logging - does anyone like pulling data out of PerfMon?

Got things you'd add to that list? What would it take to make it the perfect tool for you? Or at least good enough to use? Think about that while you finish the rest of the article!

So how do maintenance plans work anyway? When you create a plan a row gets inserted into MSDB into the sysdbmaintplans table. Then depending on which databases you selected it to run against, one or more rows get added to sysdbmaintplans_databases. The column database_name will contain either "All Databases", "All System Databases", or "All 
User Databases" if you selected one of the first three options. If you selected databases manually then the name of each database gets added to the table, each row having the same plan_id that links back to sysdbmaintplans. Want to see what jobs are associated with a plan? Just look in sysdbmaintplan_jobs. All the history is contained in sysdbmaintplan_history.

Tip: If you create a plan that is based on one custom selection, you can then add/remove more database names to the plan by editing sysdbmaintplans_databases directly. You just need one row per database along with the associated plan ID. The wizard doesn't know the difference! This is an easy way to achieve the ability to use a select statement to indicate which db's should be covered by the plan.

That explains a little about where, but not how, right? All the magic is contained in xp_sqlmaint, an extended stored procedure for which we can't view the code, but a little browsing of the process list indicates that it really just passes the parameters on to sqlmaint.exe. I created a plan on my test machine, here is what I ended up with for the backup job:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0D097FED-F1C1-4850-A34C-7A83EF96A81B -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'

Have you looked at SQLMaint? Here are the options it supports directly from BOL:

Finally, exactly how do the entries get added to the tables I mentioned above? The wizard generates a surprisingly small amount of code, the biggest part will look something like this:

Nope, not fun to look at. But it works! Seriously, I'd like to hear from you. Do you use the plans or not? Why or why not? What would you like to see added or changed? Post a note in the attached discussion area, I normally reply to posts the same day.