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:

sqlmaint

[-?] |

[

[-S server_name[\instance_name]]

[-U login_ID [-P password]]

{

[ -D database_name | -PlanName

name | -PlanID guid ]

[-Rpt text_file]

[-To operator_name]

[-HtmlRpt html_file [-DelHtmlRpt

<time_period>] ]

[-RmUnusedSpace

threshold_percent free_percent]

[-CkDB | -CkDBNoIdx]

[-CkAl | -CkAlNoIdx]

[-CkCat]

[-UpdOptiStats

sample_percent]

[-RebldIdx free_space]

[-WriteHistory]

[

{-BkUpDB

[backup_path] | -BkUpLog [backup_path] }

{-BkUpMedia

{DISK

[ [-DelBkUps <time_period>]

[-CrBkSubDir

] [ -UseDefDir ]

]

TAPE

}

[-BkUpOnlyIfClean]

[-VrfyBackup]

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:

BEGIN TRANSACTION

DECLARE @ReturnCode INT

DECLARE @PlanID nchar(36)

DECLARE @JobID0 nchar(36)

DECLARE @JobID1 nchar(36)

DECLARE @JobID2 nchar(36)

DECLARE @JobID3 nchar(36)

DECLARE @JobID4 nchar(36)

DECLARE @JobID5 nchar(36)

DECLARE @JobID6 nchar(36)

DECLARE @JobID7 nchar(36)

DECLARE @JobIDD nchar(36)

SELECT @PlanID = N'D7A8F11D-8348-48E6-AA1F-B700911BAADF'

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID4 OUTPUT, @job_name = N'DB Backup Job for DB Maintenance Plan ''DB Maintenance Plan3''', @enabled = 1, @category_id = 3