SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Custom Maintenance Plans - the T-SQL way

By Mark Marinovic,

As a DBA, I am tasked with creating and deploying Maintenance Plans on new SQL Server installations frequently. Although SSIS is a robust and powerful tool, I find it rather cumbersome for repetitive and relatively simple tasks. Furthermore, I am not a big fan of GUI (the control freak in me, I guess).

At any rate, I wanted to develop a way of performing a Maintenance Plan to my exact specifications. I also wanted it to be scalable and reusable. Here is what I came up with, borne of necessity.

1 Overview

1.1 Business Need

The need arose to have a dynamic, deployable Maintenance Plan template to implement consistently across many servers. The idea behind this script is to enable the DBA to effectively manage their SQL Server instances using T-SQL without the need for installing Visual Studio, or even Management Studio, on all of the servers managed. Implementing this methodology saves time and overhead as having to replicate Maintenance Plans via Integration Services (SSIS packages) can be cumbersome and inconsistent.

Furthermore, the Custom Maintenance Plan will allow for specialized business rules, such as "Only maintain the n most recently created User databases", or "Exclude all databases that begin with U_TEMP_%". Trying to keep Maintenance Plans up to speed via the native SSIS deployment would be inconvenient to say the very least. If you already have a backup strategy that involves off-site storage, and run certain databases that rarely change, much can be saved in terms of disk space used for backups.

Changes to business rules are as easy as altering a custom view of the databases on the server. All of the maintenance commands are preset but customizable by altering the Stored Procedures provided in the script.

1.2 Prerequisites

a. The SQL Server instance must be SQL Server 2005 RTM or greater.

b. Script must be run from an account with SQL Server sa privileges.

c. A SQL Server Operator named SQLServer_Admin must be created on the instance.

i. If the SQLServer_Admin Operator doesn't exist, one will be created for the instance with a fictitious SMTP address. The SMTP address will need to be updated in order to work properly.

d. Optional: If DBMail notifications are to be used, a DBMail profile by the name of SQLDefault must be set up and functioning.

2 Installation

2.1 Files Needed

1. The files used are either:

a. CustomMP.sql (the code posted here) OR
b. CustomMP_WithDBMail.sql (contact me)

As indicated, the only difference between the installations is the incorporation of DBMail with the Maintenance Plans (see prerequisites in section 1.2)

By deciding to incorporate DBMail, a pre-designated Operator receives notification of any Maintenance Plan job failures via e-mail, along with the actual SQL log file the job run produced for faster resolution.

2. Open SQL Server Management Studio (or osql, sqlcmd, etc.), connect to the instance you want to create a Maintenance Plan for, and execute the script appropriate for the installation.

3 What these scripts do

3.1 Objects created

1. The installation script, after checking prerequisites, will attempt to locate a database named Admin on the SQL Server Instance. If one is not found, a new Admin database will be created.

a. If the Admin database has to be created, it is sized to 30 Mb; 15 Mb for data, 15 Mb for log (both files 10% growth rate, unlimited). The database is placed in RESTRICTED_USER (DBO only) access mode, and is set to use the SIMPLE recovery model.

b. The database, if only used for this specific purpose, will not grow noticeably.

c. If the Admin database has to be created, the data and log files are placed in their respective default directories already established for that instance.

d. All other database options are determined by the setup of the model database on the SQL Server instance.

2. Once the Admin database is either found or created, the following database objects are created within it:

a. Table MaintenancePlanSettings
b. Table MaintenancePlanStaticDatabases
c. View vwMaintenancePlanDatabases
d. Stored Procedure procMaintenancePlan_SetOutputLog
e. Stored Procedure procMaintenancePlan_Subplan_1_CheckDB
f. Stored Procedure procMaintenancePlan_Subplan_2_Reindex
g. Stored Procedure procMaintenancePlan_Subplan_3_UpdateStats
h. Stored Procedure procMaintenancePlan_Subplan_4_CleanupHistory
i. Stored Procedure procMaintenancePlan_Subplan_5_BackupFull
j. Stored Procedure procMaintenancePlan_Subplan_6_BackupTranLogs
k. Stored Procedure procMaintenancePlan_Subplan_7_CleanupBackups
l. Job Category Custom Database Maintenance
m. Job CustomMaintenance.Seed Maintenance Plan Output Log Files
n. Job CustomMaintenance.Subplan_1_CheckDB
o. Job CustomMaintenance.Subplan_2_Reindex
p. Job CustomMaintenance.Subplan_3_UpdateStats
q. Job CustomMaintenance.Subplan_4_CleanupHistory
r. Job CustomMaintenance.Subplan_5_BackupFull
s. Job CustomMaintenance.Subplan_6_BackupTranLogs
t. Job CustomMaintenance.Subplan_7_CleanupBackups

4 What these objects do

4.1 Object functionality / purpose

a. Table MaintenancePlanSettings

This table stores settings for individual Maintenance Plan definitions. You may have an infinite number of Plans per instance, but by default only one exists for the instance as a whole.

You may want to have separate Maintenance Plan definitions for System databases and User databases, for instance.

i. MaintenancePlanID - The ID of the Maintenance Plan to execute. By default, the script installs Maintenance Plan ID 0, which is called by all jobs initially. If you elect to create multiple Plans per instance, new rows can be added and jobs duplicated or updated.
ii. MaintenancePlanName - By default, PlanID 0 has a name of default. Additional Plans must have a unique name.
iii. PurgeDayCountTextReports - The number of days to retain *.txt log reports in your SQL \...\LOG directory. Default value is 28 (4 weeks) and must be greater than 0.
iv. PurgeDayCountHistory - The number of days to retain job history in the msdb database. Default value is 28 (4 weeks) and must be greater than 0.
v. PurgeDayCountBAKFiles - The number of days to retain *.bak full backup files in your SQL \...\Backup directory. Default value is 2 days and must be greater than 0.
vi. PurgeDayCountTRNFiles - The number of days to retain *.trn t-log backup files in your SQL \...\Backup directory. Default value is 2 days and must be greater than 0.
vii. BackupFilePath - The path (with or without trailing '\') where your backups (*.bak and *.trn) will be stored. By default, the value is the instance default Backup path.
viii. OutputLogFilePath - The path (with or without trailing '\') where your *.txt job output logs will be stored. By default, the value is the instance default Error Log path.
ix. LatestNDB - The number of user databases (databases other than those specified as I(ncluded) in Table MaintenancePlanStaticDatabases per Plan ID) that the Maintenance Plan will affect, sorted by most recent Create Date. The default value is 0 (all user databases).
x. IndexFillFactor - The default Fill Factor for rebuilding indexes. The default value is 85.
b. Table MaintenancePlanStaticDatabases

This table stores database names tied to individual Maintenance Plan definitions. Valid database names tied to a Maintenance Plan ID will always be included or excluded (based on the database disposition) in the execution of that Plan ID, regardless of custom business rules.

For instance, if you always want to include System databases in the plan, you must list them in this table with a disposition of I for the Maintenance Plan ID you are executing.

i. FK_MaintenancePlanID - The ID of the Maintenance Plan for which the specified database is always to be included/excluded in.
ii. DatabaseName - The database name which always will be included/excluded in the Maintenance Plan identified.
iii. Disposition - The database disposition within that Maintenance Plan definition. Valid values are I (Include) or E (Exclude). The default value for new data is I.

c. View vwMaintenancePlanDatabases

This view ultimately determines what databases are included in each Maintenance Plan definition every time the jobs are run. You may alter this view any way you see fit to meet business needs specific to the SQL instance.

By default after install, all System databases and all ONLINE user databases (excluding snapshots) are included in the view.

d. Stored Procedures

1. Each Stored Procedure (with the exception of procMaintenancePlan_SetOutputLog) relates to a specific Maintenance Plan task. By default, the following tasks are included:

ii. Index Rebuild
iii. Update Statistics
iv. Cleanup Maintenance Plan / Job history
v. Full Backup
vi. T-Log backup (for all specified databases also using the FULL recovery model)
vii. Cleanup backup files and output log files

Each of the above-listed procedures refers to the View vwMaintenancePlanDatabases to determine the databases affected for the Maintenance Plan ID being used, and then generates SQL code to run against each of those databases. Each command that comes with the installation is pre-written to fit most needs, but is fully customizable by altering the procedure directly in T-SQL.

For example, the DBCC CHECKDB command, by default, runs this exact command:


If, for example, you wanted to specify the PHYSICAL_ONLY option on your SQL Server 2008 instance, you would alter the Stored Procedure like below:



2. The stored procedure procMaintenancePlan_SetOutputLog will enumerate all jobs in the newly created Custom Database Maintenance job category, and direct the SQL output of the job to a timestamp-based file name.

If you installed the CustomMP_WithDBMail version of the script, this stored procedure also includes a call to attach the output file to a notification e-mail upon job failure.

The output file naming convention is <<Job Name>>_YYYYMMDDHHMMSS.txt. The files will be saved to the root of the directory specified in the OutputLogFilePath column of the MaintenancePlanSettings table. Again, you may alter this Stored Procedure to accommodate your business needs.

This Stored Procedure, by default, is called once per minute to update the output file names on all jobs within the Custom Database Maintenance category.

e. Jobs

When setup is completed, a total of 8 jobs are created on your SQL Server instance. Each job correlates to one of the Stored Procedures in the section above. The job simply executes the Maintenance Stored Procedure associated with its' purpose at a given time for a given Plan ID.

All jobs created or altered by these scripts is in the Custom Database Maintenance job category. This job category is created for you upon installation.

The only attributes of the Custom Database Maintenance jobs that are dynamically set are the job output files and notification step (if applicable). Other than those attributes, you may change the schedules, descriptions, step names, etc. as these jobs act and perform like any other job on the instance and are persistent.

If you need to alter how the dynamically set properties change, alter the procMaintenancePlan_SetOutputLog stored procedure as necessary.

If you installed the CustomMP_WithDBMail version of the script, a separate step is included in the job which will notify the SQLServer_Admin Operator if the job fails, with an attachment of the actual SQL output from the script run.

Total article views: 3602 | Views in the last 30 days: 2
Related Articles

Default Isolation Level in stored procedures

Default Isolation Level in stored procedures


Name resolution of Default Schemas in Stored Procedures

Default Schemas in Stored Procedures


stored procedures in a database

stored procedures in a database


Get default values of parameters in stored procedures

Helps to get the default value of parameters from stored procedures and functions.