Custom Maintenance Plans - the T-SQL way
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:
i. DBCC CHECKDB
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:
DBCC CHECKDB('@dbName') WITH ALL_ERRORMSGS
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:
From:
<snip>
SET @strSQL = N'DBCC CHECKDB('''+@dbName+''') WITH ALL_ERRORMSGS'
</snip>
To:
<snip>
SET @strSQL = N'DBCC CHECKDB('''+@dbName+''') WITH ALL_ERRORMSGS, PHYSICAL_ONLY'
</snip>
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.
USE [master]
GO
IF IS_SRVROLEMEMBER('sysadmin') = 1
BEGIN
--Check prerequisites
IF NOT EXISTS(SELECT 'X' FROM msdb..sysoperators O WHERE O.[name] = 'SQLServer_Admin')
BEGIN
--Add Operator
EXEC msdb.dbo.sp_add_operator @name=N'SQLServer_Admin', @enabled=1, @pager_days=0, @email_address=N'name@domain.com'
END
--Create Admin database if it does not exist
IF NOT EXISTS(SELECT 'X' FROM sys.databases SDB WHERE SDB.[name] = 'Admin')
BEGIN
PRINT 'Creating new "Admin" database on server ' + @@SERVERNAME + '...'
DECLARE
@RegPathParams SYSNAME,
@Arg SYSNAME,
@Param SYSNAME,
@n INTEGER,
@strSQL NVARCHAR(1000),
@DefaultDBDataPath NVARCHAR(512),
@DefaultDBLogPath NVARCHAR(512)
--Find default database Data path
PRINT 'Determining default Database Data directory on server ' + @@SERVERNAME + '...'
SET @n = 0
SET @RegPathParams = N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
SET @Param = ''
WHILE(NOT @Param IS NULL)
BEGIN
SET @Param = NULL
SET @Arg = 'SqlArg' + CONVERT(NVARCHAR(100), @n)
EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @RegPathParams, @Arg, @Param OUTPUT
IF(@Param like '-d%')
BEGIN
SET @Param = SUBSTRING(@Param, 3, 255)
SET @DefaultDBDataPath = SUBSTRING(@Param, 1, LEN(@Param) - CHARINDEX('\', REVERSE(@Param)))
BREAK
END
SET @n = @n + 1
END
--Find default database Log directory
PRINT 'Determining default Database Log directory on server ' + @@SERVERNAME + '...'
EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultDBLogPath OUTPUT
--Create Admin database
SET @strSQL = 'CREATE
DATABASE [Admin] ON (NAME = N''Admin_Data'', FILENAME = N''' + @DefaultDBDataPath + '\Admin_Data.mdf'', SIZE = 15MB, FILEGROWTH = 10%)
LOG ON (NAME = N''Admin_Log'', FILENAME = N''' + COALESCE(@DefaultDBLogPath, @DefaultDBDataPath) + '\Admin_Log.ldf'', SIZE = 15MB, FILEGROWTH = 10%)'
EXEC sp_executesql @strSQL
ALTER DATABASE [Admin] SET RESTRICTED_USER
ALTER DATABASE [Admin] SET RECOVERY SIMPLE
EXEC [Admin]..sp_changedbowner 'sa'
END
END
ELSE
BEGIN
RAISERROR('Please log in as a user with sysadmin (sa) privileges.', 20, 1) WITH LOG
RETURN
END
GO
USE [Admin]
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'MaintenancePlanStaticDatabases' AND O.[type_desc] = 'USER_TABLE')
DROP TABLE [dbo].[MaintenancePlanStaticDatabases]
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'MaintenancePlanSettings' AND O.[type_desc] = 'USER_TABLE')
DROP TABLE [dbo].[MaintenancePlanSettings]
GO
DECLARE
@RegPathParams SYSNAME,
@Arg SYSNAME,
@Param SYSNAME,
@n INTEGER,
@BackupDirectory NVARCHAR(512),
@ErrorLogPath NVARCHAR(512)
--Find default Error Log path
PRINT 'Determining default Error Log directory on server ' + @@SERVERNAME + '...'
SET @n = 0
SET @RegPathParams = N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
SET @Param = ''
WHILE(NOT @Param IS NULL)
BEGIN
SET @Param = NULL
SET @Arg = 'SqlArg' + CONVERT(NVARCHAR(100), @n)
EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @RegPathParams, @Arg, @Param OUTPUT
IF(@Param like '-e%')
BEGIN
SET @Param = SUBSTRING(@Param, 3, 255)
SET @ErrorLogPath = SUBSTRING(@Param, 1, LEN(@Param) - CHARINDEX('\', REVERSE(@Param)))
BREAK
END
SET @n = @n + 1
END
--Find default Backup directory
PRINT 'Determining default Database Backup directory on server ' + @@SERVERNAME + '...'
EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT
CREATE TABLE [dbo].[MaintenancePlanSettings](
[MaintenancePlanID] TINYINT IDENTITY(0,1) NOT NULL,
[MaintenancePlanName] VARCHAR(50) NOT NULL,
[PurgeDayCountTextReports] TINYINT NOT NULL,
[PurgeDayCountHistory] TINYINT NOT NULL,
[PurgeDayCountBAKFiles] TINYINT NOT NULL,
[PurgeDayCountTRNFiles] TINYINT NOT NULL,
[BackupFilePath] VARCHAR(255) NOT NULL,
[OutputLogFilePath] VARCHAR(255) NOT NULL,
[LatestNDB] SMALLINT NOT NULL,
[IndexFillFactor] TINYINT NOT NULL) ON [PRIMARY]
ALTER TABLE [dbo].[MaintenancePlanSettings] ADD CONSTRAINT [PK_MaintenancePlanSettings] PRIMARY KEY CLUSTERED([MaintenancePlanID] ASC) ON [PRIMARY]
ALTER TABLE [dbo].[MaintenancePlanSettings] ADD CONSTRAINT [UX_MaintenancePlanName] UNIQUE NONCLUSTERED([MaintenancePlanName]) ON [PRIMARY]
--Output defaults to user
PRINT 'Default Text report retention set at 28 days.'
PRINT 'Default Job History retention set at 28 days.'
PRINT 'Default *.BAK file retention set at 2 days.'
PRINT 'Default *.TRN file retention set at 2 days.'
PRINT 'Default databases subject to Maintenance Plan: 0 (all databases).'
PRINT 'Default Index Fill Factor: 85%'
ALTER TABLE [dbo].[MaintenancePlanSettings] ADD CONSTRAINT [DF_PurgeDayCountTextReports] DEFAULT ((28)) FOR [PurgeDayCountTextReports]
ALTER TABLE [dbo].[MaintenancePlanSettings] ADD CONSTRAINT [DF_PurgeDayCountHistory] DEFAULT ((28)) FOR [PurgeDayCountHistory]
ALTER TABLE [dbo].[MaintenancePlanSettings] ADD CONSTRAINT [DF_PurgeDayCountBAKFiles] DEFAULT ((2)) FOR [PurgeDayCountBAKFiles]
ALTER TABLE [dbo].[MaintenancePlanSettings] ADD CONSTRAINT [DF_PurgeDayCountTRNFiles] DEFAULT ((2)) FOR [PurgeDayCountTRNFiles]
ALTER TABLE [dbo].[MaintenancePlanSettings] ADD CONSTRAINT [DF_LatestNDB] DEFAULT ((0)) FOR [LatestNDB]
ALTER TABLE [dbo].[MaintenancePlanSettings] ADD CONSTRAINT [DF_IndexFillFactor] DEFAULT ((85)) FOR [IndexFillFactor]
ALTER TABLE [dbo].[MaintenancePlanSettings] WITH CHECK ADD CONSTRAINT [CK_IndexFillFactor] CHECK (([IndexFillFactor]>=(0) AND [IndexFillFactor]<=(100)))
ALTER TABLE [dbo].[MaintenancePlanSettings] WITH CHECK ADD CONSTRAINT [CK_LatestNDB] CHECK (([LatestNDB]>=(0)))
ALTER TABLE [dbo].[MaintenancePlanSettings] WITH CHECK ADD CONSTRAINT [CK_PurgeDayCountBAKFiles] CHECK (([PurgeDayCountBAKFiles]>(0)))
ALTER TABLE [dbo].[MaintenancePlanSettings] WITH CHECK ADD CONSTRAINT [CK_PurgeDayCountHistory] CHECK (([PurgeDayCountHistory]>(0)))
ALTER TABLE [dbo].[MaintenancePlanSettings] WITH CHECK ADD CONSTRAINT [CK_PurgeDayCountTextReports] CHECK (([PurgeDayCountTextReports]>(0)))
ALTER TABLE [dbo].[MaintenancePlanSettings] WITH CHECK ADD CONSTRAINT [CK_PurgeDayCountTRNFiles] CHECK (([PurgeDayCountTRNFiles]>(0)))
--Create default Maintenance Plan
INSERT INTO dbo.MaintenancePlanSettings([MaintenancePlanName], [BackupFilePath], [OutputLogFilePath])
VALUES('default', @BackupDirectory, @ErrorLogPath)
GO
CREATE TABLE [dbo].[MaintenancePlanStaticDatabases](
[FK_MaintenancePlanID] TINYINT NOT NULL,
[DatabaseName] VARCHAR(50) NOT NULL,
[Disposition] CHAR(1) NOT NULL) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MaintenancePlanStaticDatabases] ADD CONSTRAINT [FK_MaintenancePlanStaticDatabases_MaintenancePlanSettings] FOREIGN KEY ([FK_MaintenancePlanID]) REFERENCES [dbo].[MaintenancePlanSettings](MaintenancePlanID)
ALTER TABLE [dbo].[MaintenancePlanStaticDatabases] ADD CONSTRAINT [UX_MaintenancePlanStaticDatabases] UNIQUE NONCLUSTERED([FK_MaintenancePlanID], [DatabaseName]) ON [PRIMARY]
ALTER TABLE [dbo].[MaintenancePlanStaticDatabases] ADD CONSTRAINT [DF_Disposition] DEFAULT ('I') FOR [Disposition]
ALTER TABLE [dbo].[MaintenancePlanStaticDatabases] WITH CHECK ADD CONSTRAINT [CK_Disposition] CHECK (([Disposition]='E' OR [Disposition]='I'))
GO
--Set databases always subject to the Maintenance Plan
INSERT INTO [dbo].[MaintenancePlanStaticDatabases]([FK_MaintenancePlanID], [DatabaseName], [Disposition]) VALUES(0, 'Admin', 'I')
INSERT INTO [dbo].[MaintenancePlanStaticDatabases]([FK_MaintenancePlanID], [DatabaseName], [Disposition]) VALUES(0, 'master', 'I')
INSERT INTO [dbo].[MaintenancePlanStaticDatabases]([FK_MaintenancePlanID], [DatabaseName], [Disposition]) VALUES(0, 'model', 'I')
INSERT INTO [dbo].[MaintenancePlanStaticDatabases]([FK_MaintenancePlanID], [DatabaseName], [Disposition]) VALUES(0, 'msdb', 'I')
INSERT INTO [dbo].[MaintenancePlanStaticDatabases]([FK_MaintenancePlanID], [DatabaseName], [Disposition]) VALUES(0, 'distribution', 'I')
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'vwMaintenancePlanDatabases' AND O.[type_desc] = 'VIEW')
DROP VIEW [dbo].[vwMaintenancePlanDatabases]
GO
CREATE VIEW [dbo].[vwMaintenancePlanDatabases]
AS
SELECT DBRank.MaintenancePlanID, DBRank.DatabaseName FROM(
SELECT
MP.MaintenancePlanID,
SDB.[name] AS DatabaseName,
RANK() OVER (ORDER BY SDB.create_date DESC) AS DatabaseRank,
MP.LatestNDB
FROM sys.databases SDB
CROSS JOIN dbo.MaintenancePlanSettings MP
WHERE
--Exclude system databases
SDB.[name] NOT IN('Admin','master','model','msdb','tempdb','distribution')
--Exclude database snapshots
AND SDB.source_database_id IS NULL
--Other status checks
AND SDB.state_desc = 'ONLINE') DBRank
WHERE
--32,767 is uppermost limit for SMALLINT data type used for this column (NDB = 0 represents ALL user databases on the instance)
DBRank.DatabaseRank <= CASE WHEN DBRank.LatestNDB = 0 THEN 32767 ELSE DBRank.LatestNDB END
UNION
--Automatically include databases with an "I" (Include) disposition by default
SELECT
MPSD.FK_MaintenancePlanID,
MPSD.DatabaseName
FROM dbo.MaintenancePlanStaticDatabases MPSD
--Validate database names with JOIN
INNER JOIN sys.databases SDB ON SDB.[name] = MPSD.DatabaseName
WHERE
MPSD.Disposition = 'I'
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'procMaintenancePlan_SetOutputLog' AND O.[type_desc] = 'SQL_STORED_PROCEDURE')
DROP PROCEDURE [dbo].[procMaintenancePlan_SetOutputLog]
GO
CREATE PROCEDURE [dbo].[procMaintenancePlan_SetOutputLog](
@MaintenancePlanID INTEGER
) AS
SET NOCOUNT ON
DECLARE
@JobID VARCHAR(255),
@OutputFileName VARCHAR(255),
@strSQL NVARCHAR(255)
DECLARE sJobs CURSOR FAST_FORWARD
--Find jobs associated with the "Custom Database Maintenance" category
FOR
SELECT
SJ.[job_id] AS JobID,
CASE WHEN CHARINDEX('\', REVERSE(MP.OutputLogFilePath)) = 1 THEN MP.OutputLogFilePath ELSE MP.OutputLogFilePath + '\' END +
SJ.[name] + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 121), ' ', ''), ':', ''), '-', '') + 'txt' AS OutputLogFileName
FROM msdb..sysjobs SJ
INNER JOIN msdb..syscategories SC ON SC.category_id = SJ.category_id
CROSS JOIN dbo.MaintenancePlanSettings MP
WHERE
SC.[name] = 'Custom Database Maintenance'
--If MP ID is passed in as a NULL, default MP ID = 0
AND MP.MaintenancePlanID = COALESCE(@MaintenancePlanID, 0)
OPEN sJobs
FETCH sJobs INTO @JobID, @OutputFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @JobID = RTRIM(@JobID)
SET @OutputFileName = RTRIM(@OutputFileName)
--Change output files to custom, per-run file
SET @strSQL = N'EXEC msdb.dbo.sp_update_jobstep @job_id = ''' + @JobID + ''', @step_id = 1, @output_file_name = ''' + @OutputFileName + ''''
EXEC sp_executesql @strSQL
FETCH sJobs INTO @JobID, @OutputFileName
END
CLOSE sJobs
DEALLOCATE sJobs
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'procMaintenancePlan_Subplan_7_CleanupBackups' AND O.[type_desc] = 'SQL_STORED_PROCEDURE')
DROP PROCEDURE [dbo].[procMaintenancePlan_Subplan_7_CleanupBackups]
GO
CREATE PROCEDURE [dbo].[procMaintenancePlan_Subplan_7_CleanupBackups](
@MaintenancePlanID INTEGER
) AS
SET NOCOUNT ON
DECLARE
@PurgeDayCountBAK TINYINT,
@PurgeDateBAK DATETIME,
@PurgeDayCountTRN TINYINT,
@PurgeDateTRN DATETIME,
@BackupFilePath NVARCHAR(255),
@strSQL NVARCHAR(255)
--If MP ID is passed in as a NULL, default MP ID = 0
SET @MaintenancePlanID = COALESCE(@MaintenancePlanID, 0)
IF NOT EXISTS(SELECT 'X' FROM dbo.MaintenancePlanSettings WHERE MaintenancePlanID = @MaintenancePlanID)
BEGIN
RAISERROR('Maintenance Plan ID %d does not exist.', 16, 1, @MaintenancePlanID)
RETURN
END
SELECT @PurgeDayCountBAK = MP.PurgeDayCountBAKFiles FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
SELECT @PurgeDayCountTRN = MP.PurgeDayCountTRNFiles FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
SELECT @BackupFilePath = MP.BackupFilePath FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
SET @PurgeDateBAK = GETDATE() - @PurgeDayCountBAK
SET @PurgeDateTRN = GETDATE() - @PurgeDayCountTRN
--Purge aged backup and t-log files
PRINT CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) + ': Deleting aged Full Backup files' + '***************'
SET @strSQL = 'EXEC master..xp_delete_file 0, ''' + @BackupFilePath + ''', N''bak'', ''' + CONVERT(VARCHAR(25), @PurgeDateBAK, 121) + ''''
PRINT 'SQL sent: '+ @strSQL
EXEC sp_executesql @strSQL
PRINT CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) + ': Deleting aged Transaction Log files' + '***************'
SET @strSQL = 'EXEC master..xp_delete_file 0, ''' + @BackupFilePath + ''', N''trn'', ''' + CONVERT(VARCHAR(25), @PurgeDateTRN, 121) + ''''
PRINT 'SQL sent: '+ @strSQL
EXEC sp_executesql @strSQL
PRINT CHAR(10) + 'Maintenance cleanup finished: ' + CONVERT(VARCHAR(25), GETDATE(), 121)
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'procMaintenancePlan_Subplan_6_BackupTranLogs' AND O.[type_desc] = 'SQL_STORED_PROCEDURE')
DROP PROCEDURE [dbo].[procMaintenancePlan_Subplan_6_BackupTranLogs]
GO
CREATE PROCEDURE [dbo].[procMaintenancePlan_Subplan_6_BackupTranLogs](
@MaintenancePlanID INTEGER
) AS
SET NOCOUNT ON
DECLARE
@dbName VARCHAR(255),
@strSQL NVARCHAR(255),
@bakLocation VARCHAR(255)
--If MP ID is passed in as a NULL, default MP ID = 0
SET @MaintenancePlanID = COALESCE(@MaintenancePlanID, 0)
SELECT @bakLocation = MP.BackupFilePath FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
IF NOT EXISTS(SELECT 'X' FROM dbo.MaintenancePlanSettings WHERE MaintenancePlanID = @MaintenancePlanID)
BEGIN
RAISERROR('Maintenance Plan ID %d does not exist.', 16, 1, @MaintenancePlanID)
RETURN
END
--Add trailing backwhack if it is missing from the end of the string
IF CHARINDEX('\', REVERSE(@bakLocation)) > 1
BEGIN
SET @bakLocation = @bakLocation + '\'
END
DECLARE sDatabases CURSOR FAST_FORWARD
FOR SELECT MP.DatabaseName FROM dbo.vwMaintenancePlanDatabases MP
INNER JOIN sys.databases SDB ON SDB.[name] = MP.DatabaseName
--Backup tran logs only for those databases chosen that *also* have recovery model of FULL
WHERE SDB.recovery_model_desc = 'FULL' AND MP.MaintenancePlanID = @MaintenancePlanID
OPEN sDatabases
FETCH sDatabases INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = RTRIM(@dbName)
SET @strSQL = N'BACKUP LOG [' + @dbName + '] TO DISK = ''' + @bakLocation + @dbName+ '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 121), ' ', ''), ':', ''), '-', '') + 'trn''
WITH NOFORMAT, INIT, NAME = ''' + @dbName + ' - Transaction Log backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
EXEC sp_executesql @strSQL
FETCH sDatabases INTO @dbName
END
CLOSE sDatabases
DEALLOCATE sDatabases
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'procMaintenancePlan_Subplan_5_BackupFull' AND O.[type_desc] = 'SQL_STORED_PROCEDURE')
DROP PROCEDURE [dbo].[procMaintenancePlan_Subplan_5_BackupFull]
GO
CREATE PROCEDURE [dbo].[procMaintenancePlan_Subplan_5_BackupFull](
@MaintenancePlanID INTEGER
) AS
SET NOCOUNT ON
DECLARE
@dbName VARCHAR(255),
@strSQL NVARCHAR(255),
@bakLocation VARCHAR(255)
--If MP ID is passed in as a NULL, default MP ID = 0
SET @MaintenancePlanID = COALESCE(@MaintenancePlanID, 0)
SELECT @bakLocation = MP.BackupFilePath FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
IF NOT EXISTS(SELECT 'X' FROM dbo.MaintenancePlanSettings WHERE MaintenancePlanID = @MaintenancePlanID)
BEGIN
RAISERROR('Maintenance Plan ID %d does not exist.', 16, 1, @MaintenancePlanID)
RETURN
END
--Add trailing backwhack if it is missing from the end of the string
IF CHARINDEX('\', REVERSE(@bakLocation)) > 1
BEGIN
SET @bakLocation = @bakLocation + '\'
END
DECLARE sDatabases CURSOR FAST_FORWARD
FOR SELECT MP.DatabaseName FROM dbo.vwMaintenancePlanDatabases MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
OPEN sDatabases
FETCH sDatabases INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = RTRIM(@dbName)
SET @strSQL = N'BACKUP DATABASE [' + @dbName + '] TO DISK = ''' + @bakLocation + @dbName+ '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 121), ' ', ''), ':', ''), '-', '') + 'bak''
WITH NOFORMAT, INIT, NAME = ''' + @dbName + ' - Full database backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
EXEC sp_executesql @strSQL
FETCH sDatabases INTO @dbName
END
CLOSE sDatabases
DEALLOCATE sDatabases
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'procMaintenancePlan_Subplan_4_CleanupHistory' AND O.[type_desc] = 'SQL_STORED_PROCEDURE')
DROP PROCEDURE [dbo].[procMaintenancePlan_Subplan_4_CleanupHistory]
GO
CREATE PROCEDURE [dbo].[procMaintenancePlan_Subplan_4_CleanupHistory](
@MaintenancePlanID INTEGER
) AS
SET NOCOUNT ON
DECLARE
@PurgeDayCountHistory TINYINT,
@PurgeDateHistory DATETIME,
@PurgeDayCountTextReports TINYINT,
@PurgeDateTextReports DATETIME,
@TextReportFilePath NVARCHAR(255),
@strSQL NVARCHAR(255)
--If MP ID is passed in as a NULL, default MP ID = 0
SET @MaintenancePlanID = COALESCE(@MaintenancePlanID, 0)
IF NOT EXISTS(SELECT 'X' FROM dbo.MaintenancePlanSettings WHERE MaintenancePlanID = @MaintenancePlanID)
BEGIN
RAISERROR('Maintenance Plan ID %d does not exist.', 16, 1, @MaintenancePlanID)
RETURN
END
SELECT @PurgeDayCountHistory = MP.PurgeDayCountHistory FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
SELECT @PurgeDayCountTextReports = MP.PurgeDayCountTextReports FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
SELECT @TextReportFilePath = MP.OutputLogFilePath FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
SET @PurgeDateHistory = GETDATE() - @PurgeDayCountHistory
SET @PurgeDateTextReports = GETDATE() - @PurgeDayCountTextReports
--Purge aged db backup and job history
PRINT CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) + ': Purging msdb backup history' + '***************'
SET @strSQL = 'EXEC msdb..sp_delete_backuphistory ''' + CONVERT(VARCHAR(25), @PurgeDateHistory, 121) + ''''
PRINT 'SQL sent: '+ @strSQL
EXEC sp_executesql @strSQL
PRINT CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) + ': Purging msdb job history' + '***************'
SET @strSQL = 'EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = ''' + CONVERT(VARCHAR(25), @PurgeDateHistory, 121) + ''''
PRINT 'SQL sent: '+ @strSQL
EXEC sp_executesql @strSQL
--Purge aged job output text reports
PRINT CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) + ': Deleting aged text reports' + '***************'
SET @strSQL = 'EXEC master..xp_delete_file 0, ''' + @TextReportFilePath + ''', N''txt'', ''' + CONVERT(VARCHAR(25), @PurgeDateTextReports, 121) + ''''
PRINT 'SQL sent: '+ @strSQL
EXEC sp_executesql @strSQL
PRINT CHAR(10) + 'Maintenance cleanup finished: ' + CONVERT(VARCHAR(25), GETDATE(), 121)
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'procMaintenancePlan_Subplan_3_UpdateStats' AND O.[type_desc] = 'SQL_STORED_PROCEDURE')
DROP PROCEDURE [dbo].[procMaintenancePlan_Subplan_3_UpdateStats]
GO
CREATE PROCEDURE [dbo].[procMaintenancePlan_Subplan_3_UpdateStats](
@MaintenancePlanID INTEGER
) AS
SET NOCOUNT ON
DECLARE
@dbName VARCHAR(255),
@strSQL NVARCHAR(255)
--If MP ID is passed in as a NULL, default MP ID = 0
SET @MaintenancePlanID = COALESCE(@MaintenancePlanID, 0)
IF NOT EXISTS(SELECT 'X' FROM dbo.MaintenancePlanSettings WHERE MaintenancePlanID = @MaintenancePlanID)
BEGIN
RAISERROR('Maintenance Plan ID %d does not exist.', 16, 1, @MaintenancePlanID)
RETURN
END
DECLARE sDatabases CURSOR FAST_FORWARD
FOR SELECT MP.DatabaseName FROM dbo.vwMaintenancePlanDatabases MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
OPEN sDatabases
FETCH sDatabases INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = RTRIM(@dbName)
PRINT CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) +
': UPDATING STATISTICS FOR DATABASE [' + @dbName + ']' + '***************' + CHAR(10)
SET @strSQL = N'EXEC [' + @dbName + ']..sp_updatestats'
EXEC sp_executesql @strSQL
FETCH sDatabases INTO @dbName
END
CLOSE sDatabases
DEALLOCATE sDatabases
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'procMaintenancePlan_Subplan_2_Reindex' AND O.[type_desc] = 'SQL_STORED_PROCEDURE')
DROP PROCEDURE [dbo].[procMaintenancePlan_Subplan_2_Reindex]
GO
CREATE PROCEDURE [dbo].[procMaintenancePlan_Subplan_2_Reindex](
@MaintenancePlanID INTEGER
) AS
SET NOCOUNT ON
DECLARE
@dbName VARCHAR(255),
@strSQL NVARCHAR(255),
@IndexFillFactor TINYINT
--If MP ID is passed in as a NULL, default MP ID = 0
SET @MaintenancePlanID = COALESCE(@MaintenancePlanID, 0)
SELECT @IndexFillFactor = MP.IndexFillFactor FROM dbo.MaintenancePlanSettings MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
IF NOT EXISTS(SELECT 'X' FROM dbo.MaintenancePlanSettings WHERE MaintenancePlanID = @MaintenancePlanID)
BEGIN
RAISERROR('Maintenance Plan ID %d does not exist.', 16, 1, @MaintenancePlanID)
RETURN
END
IF @IndexFillFactor IS NULL
BEGIN
RAISERROR('Invalid Index fill factor.', 16, 1)
RETURN
END
DECLARE sDatabases CURSOR FAST_FORWARD
FOR SELECT MP.DatabaseName FROM dbo.vwMaintenancePlanDatabases MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
OPEN sDatabases
FETCH sDatabases INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = RTRIM(@dbName)
PRINT CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) +
': STARTED INDEX REBUILDS FOR DATABASE [' + @dbName + ']' + '***************' + CHAR(10)
SET @strSQL = N'EXEC [' + @dbName + ']..sp_msForEachTable ''ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = ' + CAST(@IndexFillFactor AS VARCHAR(3)) + ', SORT_IN_TEMPDB = OFF, STATISTICS_NORECOMPUTE = OFF)'''
EXEC sp_executesql @strSQL
FETCH sDatabases INTO @dbName
END
CLOSE sDatabases
DEALLOCATE sDatabases
GO
IF EXISTS(SELECT 'X' FROM sys.objects O WHERE O.[name] = 'procMaintenancePlan_Subplan_1_CheckDB' AND O.[type_desc] = 'SQL_STORED_PROCEDURE')
DROP PROCEDURE [dbo].[procMaintenancePlan_Subplan_1_CheckDB]
GO
CREATE PROCEDURE [dbo].[procMaintenancePlan_Subplan_1_CheckDB](
@MaintenancePlanID INTEGER
) AS
SET NOCOUNT ON
DECLARE
@dbName VARCHAR(255),
@strSQL NVARCHAR(255)
--If MP ID is passed in as a NULL, default MP ID = 0
SET @MaintenancePlanID = COALESCE(@MaintenancePlanID, 0)
IF NOT EXISTS(SELECT 'X' FROM dbo.MaintenancePlanSettings WHERE MaintenancePlanID = @MaintenancePlanID)
BEGIN
RAISERROR('Maintenance Plan ID %d does not exist.', 16, 1, @MaintenancePlanID)
RETURN
END
DECLARE sDatabases CURSOR FAST_FORWARD
FOR SELECT MP.DatabaseName FROM dbo.vwMaintenancePlanDatabases MP WHERE MP.MaintenancePlanID = @MaintenancePlanID
OPEN sDatabases
FETCH sDatabases INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = RTRIM(@dbName)
PRINT CHAR(10) + CHAR(10) + '***************' + CONVERT(VARCHAR(25), GETDATE(), 121) +
': PERFORMING DBCC CHECKS FOR DATABASE [' + @dbName + ']' + '***************' + CHAR(10) + CHAR(10)
SET @strSQL = N'DBCC CHECKDB('''+@dbName+''') WITH ALL_ERRORMSGS'
EXEC sp_executesql @strSQL
FETCH sDatabases INTO @dbName
END
CLOSE sDatabases
DEALLOCATE sDatabases
GO
--Add Custom Maintenance jobs
USE [msdb]
GO
DECLARE
@CategoryName VARCHAR(255),
@JobName VARCHAR(255),
@OperatorName VARCHAR(255)
SET @CategoryName = 'Custom Database Maintenance'
SET @JobName = 'CustomMaintenance.Seed Maintenance Plan Output Log Files'
SET @OperatorName = 'SQLServer_Admin'
--Add categories if they do not exist
IF NOT EXISTS(SELECT 'X' FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]' AND category_class = 1)
EXEC msdb.dbo.sp_add_category @class = 'JOB', @type = 'LOCAL', @name = '[Uncategorized (Local)]'
IF NOT EXISTS(SELECT 'X' FROM msdb.dbo.syscategories WHERE name = @CategoryName AND category_class = 1)
EXEC msdb.dbo.sp_add_category @class = 'JOB', @type = 'LOCAL', @name = @CategoryName
IF EXISTS(SELECT 'X' FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1
EXEC msdb.dbo.sp_add_job @job_name = @JobName,
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = 'This job will update all output log file names for each job in the "Custom Database Maintenance" category.',
@category_name = '[Uncategorized (Local)]',
@owner_login_name = 'sa',
@notify_email_operator_name = @OperatorName
EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, @step_name = 'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = 'TSQL',
@command = 'EXEC dbo.procMaintenancePlan_SetOutputLog @MaintenancePlanID = 0
GO',
@database_name = 'Admin',
@flags=0
EXEC msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName, @name = 'Schedule 1',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = 20090101,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = N'(local)'
SET @JobName = 'CustomMaintenance.Subplan_1_CheckDB'
IF EXISTS(SELECT 'X' FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1
EXEC msdb.dbo.sp_add_job @job_name = @JobName,
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = NULL,
@category_name = @CategoryName,
@owner_login_name = 'sa',
@notify_email_operator_name = @OperatorName
EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, @step_name = 'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = 'TSQL',
@command = 'EXEC dbo.procMaintenancePlan_Subplan_1_CheckDB @MaintenancePlanID = 0
GO',
@database_name = 'Admin',
@flags=0
EXEC msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName, @name = 'Schedule 1',
@enabled = 1,
@freq_type = 8,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20090101,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = N'(local)'
SET @JobName = 'CustomMaintenance.Subplan_2_Reindex'
IF EXISTS(SELECT 'X' FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1
EXEC msdb.dbo.sp_add_job @job_name = @JobName,
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = NULL,
@category_name = @CategoryName,
@owner_login_name = 'sa',
@notify_email_operator_name = @OperatorName
EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, @step_name = 'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = 'TSQL',
@command = 'EXEC dbo.procMaintenancePlan_Subplan_2_Reindex @MaintenancePlanID = 0
GO',
@database_name = 'Admin',
@flags=0
EXEC msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName, @name = 'Schedule 1',
@enabled = 1,
@freq_type = 8,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20090101,
@active_end_date = 99991231,
@active_start_time = 10000,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = N'(local)'
SET @JobName = 'CustomMaintenance.Subplan_3_UpdateStats'
IF EXISTS(SELECT 'X' FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1
EXEC msdb.dbo.sp_add_job @job_name = @JobName,
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = NULL,
@category_name = @CategoryName,
@owner_login_name = 'sa',
@notify_email_operator_name = @OperatorName
EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, @step_name = 'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = 'TSQL',
@command = 'EXEC dbo.procMaintenancePlan_Subplan_3_UpdateStats @MaintenancePlanID = 0
GO',
@database_name = 'Admin',
@flags=0
EXEC msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName, @name = 'Schedule 1',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20090101,
@active_end_date = 99991231,
@active_start_time = 20000,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = N'(local)'
SET @JobName = 'CustomMaintenance.Subplan_4_CleanupHistory'
IF EXISTS(SELECT 'X' FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1
EXEC msdb.dbo.sp_add_job @job_name = @JobName,
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = NULL,
@category_name = @CategoryName,
@owner_login_name = 'sa',
@notify_email_operator_name = @OperatorName
EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, @step_name = 'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = 'TSQL',
@command = 'EXEC dbo.procMaintenancePlan_Subplan_4_CleanupHistory @MaintenancePlanID = 0
GO',
@database_name = 'Admin',
@flags=0
EXEC msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName, @name = 'Schedule 1',
@enabled = 1,
@freq_type = 8,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20090101,
@active_end_date = 99991231,
@active_start_time = 30000,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = N'(local)'
SET @JobName = 'CustomMaintenance.Subplan_5_BackupFull'
IF EXISTS(SELECT 'X' FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1
EXEC msdb.dbo.sp_add_job @job_name = @JobName,
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = NULL,
@category_name = @CategoryName,
@owner_login_name = 'sa',
@notify_email_operator_name = @OperatorName
EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, @step_name = 'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = 'TSQL',
@command = 'EXEC dbo.procMaintenancePlan_Subplan_5_BackupFull @MaintenancePlanID = 0
GO',
@database_name = 'Admin',
@flags=0
EXEC msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName, @name = 'Schedule 1',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20090101,
@active_end_date = 99991231,
@active_start_time = 200000,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = N'(local)'
SET @JobName = 'CustomMaintenance.Subplan_6_BackupTranLogs'
IF EXISTS(SELECT 'X' FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1
EXEC msdb.dbo.sp_add_job @job_name = @JobName,
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = NULL,
@category_name = @CategoryName,
@owner_login_name = 'sa',
@notify_email_operator_name = @OperatorName
EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, @step_name = 'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = 'TSQL',
@command = 'EXEC dbo.procMaintenancePlan_Subplan_6_BackupTranLogs @MaintenancePlanID = 0
GO',
@database_name = 'Admin',
@flags=0
EXEC msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName, @name = 'Schedule 1',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 8,
@freq_subday_interval = 2,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = 20090101,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = N'(local)'
SET @JobName = 'CustomMaintenance.Subplan_7_CleanupBackups'
IF EXISTS(SELECT 'X' FROM msdb.dbo.sysjobs_view WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1
EXEC msdb.dbo.sp_add_job @job_name = @JobName,
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = NULL,
@category_name = @CategoryName,
@owner_login_name = 'sa',
@notify_email_operator_name = @OperatorName
EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName, @step_name = 'Step 1',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = 'TSQL',
@command = 'EXEC dbo.procMaintenancePlan_Subplan_7_CleanupBackups @MaintenancePlanID = 0
GO',
@database_name = 'Admin',
@flags=0
EXEC msdb.dbo.sp_update_job @job_name = @JobName, @start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName, @name = 'Schedule 1',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20090101,
@active_end_date = 99991231,
@active_start_time = 195500,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName, @server_name = N'(local)'
GO 
