Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Maintenance Plans

By Andy Warren, (first published: 2002/04/11)

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.

Total article views: 33814 | Views in the last 30 days: 23
 
Related Articles
FORUM

Execution Error

Execution error while executing the procedure

FORUM

URGENT: Error executing scripts......

URGENT: Error executing scripts......

FORUM

Error Executing the package.

Error executing the package , pls help.

FORUM

Error accessing Active Directory form code

Error accessing Active Directory form code

FORUM

Design Active Directory as a database

Design Active Directory as a database

Tags
administration    
configuring    
indexing    
miscellaneous    
performance tuning    
programming    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones