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

Rate

5 (4)

Share

Share

Rate

5 (4)