SQLServerCentral Article

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

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID4, @step_id = 1, @step_name = N'Step 1', @subsystem = N'TSQL', @on_success_action = 1, @on_fail_action = 2, @command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID D7A8F11D-8348-48E6-AA1F-B700911BAADF -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan34.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"''', @flags = 4

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID4, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 0, @active_start_time = 20000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID4, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

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

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID6, @step_id = 1, @step_name = N'Step 1', @subsystem = N'TSQL', @on_success_action = 1, @on_fail_action = 2, @command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID D7A8F11D-8348-48E6-AA1F-B700911BAADF -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan36.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -BkExt "TRN"''', @flags = 4

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID6, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 0, @active_start_time = 0, @freq_interval = 126, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID6, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

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

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID2, @step_id = 1, @step_name = N'Step 1', @subsystem = N'TSQL', @on_success_action = 1, @on_fail_action = 2, @command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID D7A8F11D-8348-48E6-AA1F-B700911BAADF -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan32.txt" -WriteHistory -CkDB ''', @flags = 4

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID2, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 0, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID2, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

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

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID0, @step_id = 1, @step_name = N'Step 1', @subsystem = N'TSQL', @on_success_action = 1, @on_fail_action = 2, @command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID D7A8F11D-8348-48E6-AA1F-B700911BAADF -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan30.txt" -WriteHistory -RebldIdx 10 ''', @flags = 4

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID0, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 0, @active_start_time = 10000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID0, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

SELECT @JobID0, @JobID1, @JobID2, @JobID3, @JobID4, @JobID5, @JobID6, @JobID7

COMMIT TRANSACTION  

DECLARE @PlanID nchar(36) 

SELECT @PlanID = N'CF635C1C-F486-442B-B63F-8B7BD62A1003' 

DECLARE @ReturnCode INT 

BEGIN TRANSACTION 

IF (SELECT COUNT(*) FROM msdb.dbo.sysdbmaintplan_databases WHERE plan_id = @PlanID AND database_name = N'DefaultDB') < 1 

INSERT msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) VALUES (@PlanID, N'DefaultDB')

DELETE FROM msdb.dbo.sysdbmaintplan_jobs WHERE plan_id = @PlanID

INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, N'2E8A5F49-28BB-42AF-BEED-53F2575716AA')

INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, N'95C94859-C869-472B-AE02-6DB220E5C177')

INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, N'218DAFDA-F5F1-49D1-A008-8FB89E3AE6B2')

COMMIT TRANSACTION

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.

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)