tigertoolbox/MaintenanceSolution/5_job_Maintenance_MEA.sql

USE [msdb]
GO

IF EXISTS(SELECT [object_id] FROM sys.views WHERE [name] = 'vw_MaintenanceLog')
BEGIN
	DROP VIEW vw_MaintenanceLog;
	PRINT 'View vw_MaintenanceLog dropped'
END
GO

CREATE VIEW vw_MaintenanceLog AS
SELECT [name]
	,[step_name]
    ,(SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		[log],
		NCHAR(1),N'?'),NCHAR(2),N'?'),NCHAR(3),N'?'),NCHAR(4),N'?'),NCHAR(5),N'?'),NCHAR(6),N'?'),NCHAR(7),N'?'),NCHAR(8),N'?'),NCHAR(11),N'?'),NCHAR(12),N'?'),NCHAR(14),N'?'),NCHAR(15),N'?'),NCHAR(16),N'?'),NCHAR(17),N'?'),NCHAR(18),N'?'),NCHAR(19),N'?'),NCHAR(20),N'?'),NCHAR(21),N'?'),NCHAR(22),N'?'),NCHAR(23),N'?'),NCHAR(24),N'?'),NCHAR(25),N'?'),NCHAR(26),N'?'),NCHAR(27),N'?'),NCHAR(28),N'?'),NCHAR(29),N'?'),NCHAR(30),N'?'),NCHAR(31),N'?') 
		AS [text()] FROM [msdb].[dbo].[sysjobstepslogs] sjsl2 WHERE sjsl2.log_id = sjsl.log_id FOR XML PATH(''), TYPE) AS 'Log'
	,sjsl.[date_created]
    ,sjsl.[date_modified]
    ,([log_size]/1024) AS [log_size_kb]
FROM [msdb].[dbo].[sysjobstepslogs] sjsl
INNER JOIN [msdb].[dbo].[sysjobsteps] sjs ON sjs.[step_uid] = sjsl.[step_uid]
INNER JOIN [msdb].[dbo].[sysjobs] sj ON sj.[job_id] = sjs.[job_id]
WHERE [name] = 'Weekly Maintenance';
GO

PRINT 'View vw_MaintenanceLog view created';
GO

IF OBJECTPROPERTY(OBJECT_ID('dbo.usp_CheckIntegrity'), N'IsProcedure') = 1
BEGIN
	DROP PROCEDURE dbo.usp_CheckIntegrity;
	PRINT 'Procedure usp_CheckIntegrity dropped'
END	
GO

CREATE PROCEDURE usp_CheckIntegrity @VLDBMode bit = 1, @SingleUser bit = 0, @CreateSnap bit = 1, @SnapPath NVARCHAR(1000) = NULL, @AO_Secondary bit = 0, @Physical bit = 0
AS
/* 
This checks the logical and physical integrity of all the objects in the specified database by performing the following operations: 
|-For VLDBs (larger than 1TB):
  |- On Fridays, if VLDB Mode = 0, runs DBCC CHECKALLOC.
  |- On Fridays, runs DBCC CHECKCATALOG.
  |- Everyday, if VLDB Mode = 0, runs DBCC CHECKTABLE or if VLDB Mode = 1, DBCC CHECKFILEGROUP on a subset of tables and views, divided by daily buckets.
|-For DBs smaller than 1TB:
  |- Every Friday a DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database.

To set how VLDBs are handled, set @VLDBMode to 0 = Bucket by Table Size or 1 = Bucket by Filegroup Size
Buckets are built weekly, on Friday.

IMPORTANT: Consider running DBCC CHECKDB routinely (at least, weekly). On large databases and for more frequent checks, consider using the PHYSICAL_ONLY parameter.
http://msdn.microsoft.com/en-us/library/ms176064.aspx
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx

Excludes all Offline and Read-Only DBs, and works on databases over 1TB

If a database has Read-Only filegroups, any integrity check will fail if there are other open connections to the database.
Setting @CreateSnap = 1 will create a database snapshot before running the check on the snapshot, and drop it at the end (default).
Setting @CreateSnap = 0 means the integrity check might fail if there are other open connection on the database.
Note: set a custom snapshot creation path in @SnapPath or the same path as the database in scope will be used.
Ex.: @SnapPath = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data'

If snapshots are not allowed and a database has Read-Only filegroups, any integrity check will fail if there are other openned connections to the database.
Setting @SingleUser = 1 will set the database in single user mode before running the check, and to multi user afterwards.
Setting @SingleUser = 0 means the integrity check might fail if there are other open connection on the database.

If on SQL Server 2012 or above and you are using Availability Replicas: 
Setting @AO_Secondary = 0 then AlwaysOn primary replicas are eligible for Integrity Checks, but secondary replicas are skipped.
Setting @AO_Secondary = 1 then AlwaysOn secondary replicas are eligible for Integrity Checks, but primary replicas are skipped.

If more frequent checks are required, consider using the PHYSICAL_ONLY parameter:
Setting @Physical = 0 does not consider PHYSICAL_ONLY option.
Setting @Physical = 1 enables PHYSICAL_ONLY option (where available).
*/

SET NOCOUNT ON;

IF @VLDBMode NOT IN (0,1)
BEGIN
	RAISERROR('[ERROR: Must set a integrity check strategy for any VLDBs we encounter - 0 = Bucket by Table Size; 1 = Bucket by Filegroup Size]', 16, 1, N'VLDB')
	RETURN
END

IF @CreateSnap = 1 AND @SingleUser = 1
BEGIN
	RAISERROR('[ERROR: Must select only one method of checking databases with Read-Only FGs]', 16, 1, N'ReadOnlyFGs')
	RETURN
END

DECLARE @dbid int, @dbname sysname, @sqlcmdROFG NVARCHAR(1000), @sqlcmd NVARCHAR(max), @sqlcmd_Create NVARCHAR(max), @sqlcmd_Drop NVARCHAR(500)
DECLARE @msg NVARCHAR(500), @params NVARCHAR(500), @sqlcmd_AO NVARCHAR(4000);
DECLARE @filename sysname, @filecreateid int, @Message VARCHAR(1000);
DECLARE @Buckets tinyint, @BucketCnt tinyint, @BucketPages bigint, @TodayBucket tinyint, @dbsize bigint, @fg_id int, @HasROFG bigint, @sqlsnapcmd NVARCHAR(max);
DECLARE @BucketId tinyint, @object_id int, @name sysname, @schema sysname, @type CHAR(2), @type_desc NVARCHAR(60), @used_page_count bigint;
DECLARE @sqlmajorver int, @ErrorMessage NVARCHAR(4000)
IF NOT EXISTS(SELECT [object_id] FROM sys.tables WHERE [name] = 'tblDbBuckets')
CREATE TABLE tblDbBuckets (BucketId int, [database_id] int, [object_id] int, [name] sysname, [schema] sysname, [type] CHAR(2), type_desc NVARCHAR(60), used_page_count bigint, isdone bit);
IF NOT EXISTS(SELECT [object_id] FROM sys.tables WHERE [name] = 'tblFgBuckets')
CREATE TABLE tblFgBuckets (BucketId int, [database_id] int, [data_space_id] int, [name] sysname, used_page_count bigint, isdone bit);
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs'))
CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, rows_size_MB bigint, isdone bit)
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblBuckets'))
CREATE TABLE #tblBuckets (BucketId int, MaxAmount bigint, CurrentRunTotal bigint)
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblObj'))
CREATE TABLE #tblObj ([object_id] int, [name] sysname, [schema] sysname, [type] CHAR(2), type_desc NVARCHAR(60), used_page_count bigint, isdone bit)
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblFGs'))
CREATE TABLE #tblFGs ([data_space_id] int, [name] sysname, used_page_count bigint, isdone bit)
IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblSnapFiles'))
CREATE TABLE #tblSnapFiles ([name] sysname, isdone bit)

SELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);

SELECT @Message = '** Start: ' + CONVERT(VARCHAR, GETDATE())
RAISERROR(@Message, 0, 42) WITH NOWAIT;

SET @sqlcmd_AO = 'SELECT sd.database_id, sd.name, SUM((size * 8) / 1024) AS rows_size_MB, 0 
FROM sys.databases sd (NOLOCK)
INNER JOIN sys.master_files smf (NOLOCK) ON sd.database_id = smf.database_id
WHERE sd.is_read_only = 0 AND sd.state = 0 AND sd.database_id <> 2 AND smf.[type] = 0';

IF @sqlmajorver >= 11 AND @AO_Secondary = 0 -- Skip all local AlwaysOn secondary replicas
BEGIN
	SET @sqlcmd_AO = @sqlcmd_AO + CHAR(10) + 'AND sd.[database_id] NOT IN (SELECT dr.database_id FROM sys.dm_hadr_database_replica_states dr
INNER JOIN sys.dm_hadr_availability_replica_states rs ON dr.group_id = rs.group_id
INNER JOIN sys.databases d ON dr.database_id = d.database_id
WHERE rs.role = 2 -- Is Secondary
AND dr.is_local = 1
AND rs.is_local = 1)'
END;

IF @sqlmajorver >= 11 AND @AO_Secondary = 1 -- Skip all local AlwaysOn primary replicas
BEGIN
	SET @sqlcmd_AO = @sqlcmd_AO + CHAR(10) + 'AND sd.[database_id] NOT IN (SELECT dr.database_id FROM sys.dm_hadr_database_replica_states dr
INNER JOIN sys.dm_hadr_availability_replica_states rs ON dr.group_id = rs.group_id
INNER JOIN sys.databases d ON dr.database_id = d.database_id
WHERE rs.role = 1 -- Is Primary
AND dr.is_local = 1
AND rs.is_local = 0)'
END;

SET @sqlcmd_AO = @sqlcmd_AO + CHAR(10) + 'GROUP BY sd.database_id, sd.name';

INSERT INTO #tmpdbs ([dbid], [dbname], rows_size_MB, isdone)
EXEC sp_executesql @sqlcmd_AO;

WHILE (SELECT COUNT([dbid]) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
	SET @dbid = (SELECT TOP 1 [dbid] FROM #tmpdbs WHERE isdone = 0)
	SET @dbname = (SELECT TOP 1 [dbname] FROM #tmpdbs WHERE isdone = 0)
	SET @dbsize = (SELECT TOP 1 [rows_size_MB] FROM #tmpdbs WHERE isdone = 0)
	
	-- If a snapshot is to be created, set the proper path
	IF @SnapPath IS NULL
	BEGIN
		SELECT TOP 1 @SnapPath = physical_name FROM sys.master_files WHERE database_id = @dbid AND [type] = 0 AND [state] = 0
		IF @SnapPath IS NOT NULL
		BEGIN
			SELECT @SnapPath = LEFT(@SnapPath, LEN(@SnapPath)-CHARINDEX('\',REVERSE(@SnapPath)))
		END
	END;

	-- Find if database has Read-Only FGs
	SET @sqlcmd = N'USE [' + @dbname + ']; SELECT @HasROFGOUT = COUNT(data_space_id) FROM sys.filegroups WHERE is_read_only = 1'
	SET @params = N'@HasROFGOUT bigint OUTPUT';
	EXECUTE sp_executesql @sqlcmd, @params, @HasROFGOUT=@HasROFG OUTPUT;

	SET @sqlcmd = ''
	IF @dbsize < 1048576 -- smaller than 1TB
	BEGIN
		-- Is it Friday yet? If so, start database check
		IF (SELECT 32 & POWER(2, DATEPART(weekday, GETDATE())-1)) > 0
		BEGIN
			IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NOT NULL
			SELECT @msg = CHAR(10) + CONVERT(VARCHAR, GETDATE(), 9) + ' - Started integrity checks on ' + @dbname + '_CheckDB_Snapshot';
			
			IF (@HasROFG > 0 AND @SingleUser = 1) OR (@HasROFG = 0)
			SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Started integrity checks on ' + @dbname;
			
			RAISERROR (@msg, 10, 1) WITH NOWAIT

			IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NOT NULL
			SET @sqlcmd = 'DBCC CHECKDB (''' + @dbname + '_CheckDB_Snapshot'') WITH '
			
			IF (@HasROFG > 0 AND @SingleUser = 1) OR (@HasROFG = 0)
			SET @sqlcmd = 'DBCC CHECKDB (' + CONVERT(NVARCHAR(10),@dbid) + ') WITH '
			
			IF @Physical = 1
			BEGIN
				SET @sqlcmd = @sqlcmd + 'PHYSICAL_ONLY;'
			END
			ELSE
			BEGIN
				SET @sqlcmd = @sqlcmd + 'DATA_PURITY;'
			END;

			IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NOT NULL
			BEGIN
				TRUNCATE TABLE #tblSnapFiles;
				
				INSERT INTO #tblSnapFiles
				SELECT name, 0 FROM sys.master_files WHERE database_id = @dbid AND [type] = 0;
				
				SET @filecreateid = 1
				SET @sqlsnapcmd = ''

				WHILE (SELECT COUNT([name]) FROM #tblSnapFiles WHERE isdone = 0) > 0
				BEGIN
					SELECT TOP 1 @filename = [name] FROM #tblSnapFiles WHERE isdone = 0
					SET @sqlsnapcmd = @sqlsnapcmd + CHAR(10) + '(NAME = [' + @filename + '], FILENAME = ''' + @SnapPath + '\' + @dbname + '_CheckDB_Snapshot_Data_' + CONVERT(VARCHAR(10), @filecreateid) + '.ss''),'
					SET @filecreateid = @filecreateid + 1

					UPDATE #tblSnapFiles
					SET isdone = 1 WHERE [name] = @filename;
				END;

				SELECT @sqlsnapcmd = LEFT(@sqlsnapcmd, LEN(@sqlsnapcmd)-1);

				SET @sqlcmd_Create = 'USE master;
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = ''' + @dbname + '_CheckDB_Snapshot'')
CREATE DATABASE [' + @dbname + '_CheckDB_Snapshot] ON ' + @sqlsnapcmd + CHAR(10) + 'AS SNAPSHOT OF [' + @dbname + '];' 
				
				SET @sqlcmd_Drop = 'USE master; 
IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = ''' + @dbname + '_CheckDB_Snapshot'') 
DROP DATABASE [' + @dbname + '_CheckDB_Snapshot];'
			END
			
			IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NULL
			BEGIN
				SET @sqlcmd = NULL
				SELECT @Message = '** Skipping database ' + @dbname + ': Could not find a valid path to create DB snapshot - ' + CONVERT(VARCHAR, GETDATE())
				RAISERROR(@Message, 0, 42) WITH NOWAIT;
			END
			
			IF @HasROFG > 0 AND @SingleUser = 1
			BEGIN
				SET @sqlcmd = 'USE master;
ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + CHAR(10) + @sqlcmd + CHAR(10) + 
'USE master;
ALTER DATABASE [' + @dbname + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
			END

			IF @sqlcmd_Create IS NOT NULL
			BEGIN TRY
				SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Creating database snapshot ' + @dbname + '_CheckDB_Snapshot';
				RAISERROR (@msg, 10, 1) WITH NOWAIT	

				EXEC sp_executesql @sqlcmd_Create;
			END TRY
			BEGIN CATCH
				EXEC sp_executesql @sqlcmd_Drop;
				
				SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
				SELECT @ErrorMessage = 'Create Snapshot - Error raised in TRY block. ' + ERROR_MESSAGE()
				RAISERROR (@ErrorMessage, 16, 1);
			END CATCH

			IF @sqlcmd IS NOT NULL
			BEGIN TRY					
				EXEC sp_executesql @sqlcmd;

				UPDATE tblFgBuckets
				SET isdone = 1
				FROM tblFgBuckets
				WHERE [database_id] = @dbid AND [data_space_id] = @fg_id AND used_page_count = @used_page_count AND isdone = 0 AND BucketId = @TodayBucket
			END TRY
			BEGIN CATCH
				SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
				SELECT @ErrorMessage = 'Check cycle - Error raised in TRY block. ' + ERROR_MESSAGE()
				RAISERROR (@ErrorMessage, 16, 1);
				RETURN
			END CATCH
			
			IF @sqlcmd_Drop IS NOT NULL
			BEGIN TRY
				SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Droping database snapshot ' + @dbname + '_CheckDB_Snapshot';
				RAISERROR (@msg, 10, 1) WITH NOWAIT	
				EXEC sp_executesql @sqlcmd_Drop;
			END TRY
			BEGIN CATCH
				SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
				SELECT @ErrorMessage = 'Drop Snapshot - Error raised in TRY block. ' + ERROR_MESSAGE()
				RAISERROR (@ErrorMessage, 16, 1);
			END CATCH
		END
		ELSE
		BEGIN
			SELECT @Message = '** Skipping database ' + @dbname + ': Today is not Sunday - ' + CONVERT(VARCHAR, GETDATE())
			RAISERROR(@Message, 0, 42) WITH NOWAIT;
		END
	END;

	IF @dbsize >= 1048576 -- 1TB or Larger, then create buckets
	BEGIN
		-- Buckets are built on a weekly basis, so is it Friday yet? If so, start building
		IF (SELECT 32 & POWER(2, DATEPART(weekday, GETDATE())-1)) > 0
		BEGIN
			TRUNCATE TABLE #tblObj
			TRUNCATE TABLE #tblBuckets
			TRUNCATE TABLE #tblFGs
			TRUNCATE TABLE tblFgBuckets
			TRUNCATE TABLE tblDbBuckets

			SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Creating database snapshot ' + @dbname + '_CheckDB_Snapshot';
			RAISERROR (@msg, 10, 1) WITH NOWAIT				

			IF @VLDBMode = 0 -- Setup to bucketize by Table Size
			BEGIN
				SET @sqlcmd = 'SELECT so.[object_id], so.[name], ss.name, so.[type], so.type_desc, SUM(sps.used_page_count) AS used_page_count, 0
FROM [' + @dbname + '].sys.objects so
INNER JOIN [' + @dbname + '].sys.dm_db_partition_stats sps ON so.[object_id] = sps.[object_id]
INNER JOIN [' + @dbname + '].sys.indexes si ON so.[object_id] = si.[object_id]
INNER JOIN [' + @dbname + '].sys.schemas ss ON so.[schema_id] = ss.[schema_id] 
WHERE so.[type] IN (''S'', ''U'', ''V'')
GROUP BY so.[object_id], so.[name], ss.name, so.[type], so.type_desc
ORDER BY used_page_count DESC'

				INSERT INTO #tblObj
				EXEC sp_executesql @sqlcmd;
			END

			IF @VLDBMode = 1 -- Setup to bucketize by Filegroup Size
			BEGIN
				SET @sqlcmd = 'SELECT fg.data_space_id, fg.name AS [filegroup_name], SUM(sps.used_page_count) AS used_page_count, 0
FROM [' + @dbname + '].sys.dm_db_partition_stats sps
INNER JOIN [' + @dbname + '].sys.indexes i ON sps.object_id = i.object_id
INNER JOIN [' + @dbname + '].sys.partition_schemes ps ON ps.data_space_id = i.data_space_id 
INNER JOIN [' + @dbname + '].sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = sps.partition_number 
INNER JOIN [' + @dbname + '].sys.filegroups fg ON dds.data_space_id = fg.data_space_id
--WHERE fg.is_read_only = 0
GROUP BY fg.name, ps.name, fg.data_space_id
ORDER BY SUM(sps.used_page_count) DESC, fg.data_space_id'

				INSERT INTO #tblFGs
				EXEC sp_executesql @sqlcmd;
			END

			SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Bucketizing by ' + CASE WHEN @VLDBMode = 1 THEN 'Filegroup Size' ELSE 'Table Size' END;
			RAISERROR (@msg, 10, 1) WITH NOWAIT	

			-- Create buckets
			SET @Buckets = 8
			SET @BucketCnt = 1
			SET @sqlcmd = N'SELECT @BucketPagesOUT = SUM(used_page_count)/7 FROM ' + CASE WHEN @VLDBMode = 0 THEN '#tblObj' WHEN @VLDBMode = 1 THEN '#tblFGs' END
			SET @params = N'@BucketPagesOUT bigint OUTPUT';
			EXECUTE sp_executesql @sqlcmd, @params, @BucketPagesOUT=@BucketPages OUTPUT;

			WHILE @BucketCnt <> @Buckets
			BEGIN
				INSERT INTO #tblBuckets VALUES (@BucketCnt, @BucketPages, 0) 
				SET @BucketCnt = @BucketCnt + 1
			END

			IF @VLDBMode = 0 -- Populate buckets by Table Size
			BEGIN
				WHILE (SELECT COUNT(*) FROM #tblObj WHERE isdone = 0) > 0
				BEGIN
					SELECT TOP 1 @object_id = [object_id], @name = [name], @schema = [schema], @type = [type], @type_desc = type_desc, @used_page_count = used_page_count
					FROM #tblObj
					WHERE isdone = 0
					ORDER BY used_page_count DESC

					SELECT TOP 1 @BucketId = BucketId FROM #tblBuckets ORDER BY CurrentRunTotal

					INSERT INTO tblDbBuckets 
					SELECT @BucketId, @dbid, @object_id, @name, @schema, @type, @type_desc, @used_page_count, 0;

					UPDATE #tblObj
					SET isdone = 1
					FROM #tblObj
					WHERE [object_id] = @object_id AND used_page_count = @used_page_count AND isdone = 0;

					UPDATE #tblBuckets
					SET CurrentRunTotal = CurrentRunTotal + @used_page_count
					WHERE BucketId = @BucketId;
				END
			END;

			IF @VLDBMode = 1 -- Populate buckets by Filegroup Size
			BEGIN
				WHILE (SELECT COUNT(*) FROM #tblFGs WHERE isdone = 0) > 0
				BEGIN
					SELECT TOP 1 @fg_id = [data_space_id], @name = [name], @used_page_count = used_page_count
					FROM #tblFGs
					WHERE isdone = 0
					ORDER BY used_page_count DESC

					SELECT TOP 1 @BucketId = BucketId FROM #tblBuckets ORDER BY CurrentRunTotal

					INSERT INTO tblFgBuckets 
					SELECT @BucketId, @dbid, @fg_id, @name, @used_page_count, 0;

					UPDATE #tblFGs
					SET isdone = 1
					FROM #tblFGs
					WHERE [data_space_id] = @fg_id AND used_page_count = @used_page_count AND isdone = 0;

					UPDATE #tblBuckets
					SET CurrentRunTotal = CurrentRunTotal + @used_page_count
					WHERE BucketId = @BucketId;
				END
			END
		END;

		-- What day is today? 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday
		SELECT @TodayBucket = CASE WHEN 1 & POWER(2, DATEPART(weekday, GETDATE())-1) = 1 THEN 1 
				WHEN 2 & POWER(2, DATEPART(weekday, GETDATE())-1) = 2 THEN 2
				WHEN 4 & POWER(2, DATEPART(weekday, GETDATE())-1) = 4 THEN 3
				WHEN 8 & POWER(2, DATEPART(weekday, GETDATE())-1) = 8 THEN 4
				WHEN 16 & POWER(2, DATEPART(weekday, GETDATE())-1) = 16 THEN 5
				WHEN 32 & POWER(2, DATEPART(weekday, GETDATE())-1) = 32 THEN 6
				WHEN 64 & POWER(2, DATEPART(weekday, GETDATE())-1) = 64 THEN 7
			END;
		
		-- Is it Friday yet? If so, start working on allocation and catalog checks on todays bucket
		IF (SELECT 32 & POWER(2, DATEPART(weekday, GETDATE())-1)) > 0
		BEGIN
			IF @VLDBMode = 0
			BEGIN
				IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NOT NULL
				SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Started allocation checks on ' + @dbname + '_CheckDB_Snapshot]';
			
				IF (@HasROFG > 0 AND @SingleUser = 1) OR (@HasROFG = 0)
				SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Started allocation checks on ' + @dbname;

				RAISERROR (@msg, 10, 1) WITH NOWAIT

				IF @HasROFG > 0 AND @CreateSnap = 1
				SET @sqlcmd = 'DBCC CHECKALLOC (''' + @dbname + '_CheckDB_Snapshot'');'
				
				IF (@HasROFG > 0 AND @SingleUser = 1) OR (@HasROFG = 0)
				SET @sqlcmd = 'DBCC CHECKALLOC (' + CONVERT(NVARCHAR(10),@dbid) + ');'

				IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NOT NULL
				BEGIN
					TRUNCATE TABLE #tblSnapFiles;
				
					INSERT INTO #tblSnapFiles
					SELECT name, 0 FROM sys.master_files WHERE database_id = @dbid AND [type] = 0;
				
					SET @filecreateid = 1
					SET @sqlsnapcmd = ''

					WHILE (SELECT COUNT([name]) FROM #tblSnapFiles WHERE isdone = 0) > 0
					BEGIN
						SELECT TOP 1 @filename = [name] FROM #tblSnapFiles WHERE isdone = 0
						SET @sqlsnapcmd = @sqlsnapcmd + CHAR(10) + '(NAME = [' + @filename + '], FILENAME = ''' + @SnapPath + '\' + @dbname + '_CheckDB_Snapshot_Data_' + CONVERT(VARCHAR(10), @filecreateid) + '.ss''),'
						SET @filecreateid = @filecreateid + 1

						UPDATE #tblSnapFiles
						SET isdone = 1 WHERE [name] = @filename;
					END;

					SELECT @sqlsnapcmd = LEFT(@sqlsnapcmd, LEN(@sqlsnapcmd)-1);

					SET @sqlcmd_Create = 'USE master;
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = ''' + @dbname + '_CheckDB_Snapshot'')
CREATE DATABASE [' + @dbname + '_CheckDB_Snapshot] ON ' + @sqlsnapcmd + CHAR(10) + 'AS SNAPSHOT OF [' + @dbname + '];' 
				
					SET @sqlcmd_Drop = 'USE master; 
IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = ''' + @dbname + '_CheckDB_Snapshot'') 
DROP DATABASE [' + @dbname + '_CheckDB_Snapshot];'
				END
			
				IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NULL
				BEGIN
					SET @sqlcmd = NULL
					SELECT @Message = '** Skipping database ' + @dbname + ': Could not find a valid path to create DB snapshot - ' + CONVERT(VARCHAR, GETDATE())
					RAISERROR(@Message, 0, 42) WITH NOWAIT;
				END
				
				IF @HasROFG > 0 AND @SingleUser = 1
				BEGIN
					SET @sqlcmd = 'USE master;
ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + CHAR(10) + @sqlcmd + CHAR(10) + 
'USE master;
ALTER DATABASE [' + @dbname + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
				END

				IF @sqlcmd_Create IS NOT NULL
				BEGIN TRY
					SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Creating database snapshot ' + @dbname + '_CheckDB_Snapshot';
					RAISERROR (@msg, 10, 1) WITH NOWAIT	

					EXEC sp_executesql @sqlcmd_Create;
				END TRY
				BEGIN CATCH
					EXEC sp_executesql @sqlcmd_Drop;
					
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Create Snapshot - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
				END CATCH

				IF @sqlcmd IS NOT NULL
				BEGIN TRY					
					EXEC sp_executesql @sqlcmd;

					UPDATE tblFgBuckets
					SET isdone = 1
					FROM tblFgBuckets
					WHERE [database_id] = @dbid AND [data_space_id] = @fg_id AND used_page_count = @used_page_count AND isdone = 0 AND BucketId = @TodayBucket
				END TRY
				BEGIN CATCH
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Check cycle - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
					RETURN
				END CATCH
				
				IF @sqlcmd_Drop IS NOT NULL
				BEGIN TRY
					SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Droping database snapshot ' + @dbname + '_CheckDB_Snapshot';
					RAISERROR (@msg, 10, 1) WITH NOWAIT	
					EXEC sp_executesql @sqlcmd_Drop;
				END TRY
				BEGIN CATCH
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Drop Snapshot - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
				END CATCH
			END;

			IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NOT NULL
			SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Started catalog checks on ' + @dbname + '_CheckDB_Snapshot';
			
			IF (@HasROFG > 0 AND @SingleUser = 1) OR (@HasROFG = 0)
			SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Started catalog checks on ' + @dbname;

			RAISERROR (@msg, 10, 1) WITH NOWAIT

			IF @HasROFG > 0 AND @CreateSnap = 1
			SET @sqlcmd = 'DBCC CHECKCATALOG (''' + @dbname + '_CheckDB_Snapshot'');'
			
			IF (@HasROFG > 0 AND @SingleUser = 1) OR (@HasROFG = 0)
			SET @sqlcmd = 'DBCC CHECKCATALOG (' + CONVERT(NVARCHAR(10),@dbid) + ');'

			IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NOT NULL
			BEGIN
				TRUNCATE TABLE #tblSnapFiles;
				
				INSERT INTO #tblSnapFiles
				SELECT name, 0 FROM sys.master_files WHERE database_id = @dbid AND [type] = 0;
				
				SET @filecreateid = 1
				SET @sqlsnapcmd = ''

				WHILE (SELECT COUNT([name]) FROM #tblSnapFiles WHERE isdone = 0) > 0
				BEGIN
					SELECT TOP 1 @filename = [name] FROM #tblSnapFiles WHERE isdone = 0
					SET @sqlsnapcmd = @sqlsnapcmd + CHAR(10) + '(NAME = [' + @filename + '], FILENAME = ''' + @SnapPath + '\' + @dbname + '_CheckDB_Snapshot_Data_' + CONVERT(VARCHAR(10), @filecreateid) + '.ss''),'
					SET @filecreateid = @filecreateid + 1

					UPDATE #tblSnapFiles
					SET isdone = 1 WHERE [name] = @filename;
				END;

				SELECT @sqlsnapcmd = LEFT(@sqlsnapcmd, LEN(@sqlsnapcmd)-1);

				SET @sqlcmd_Create = 'USE master;
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = ''' + @dbname + '_CheckDB_Snapshot'')
CREATE DATABASE [' + @dbname + '_CheckDB_Snapshot] ON ' + @sqlsnapcmd + CHAR(10) + 'AS SNAPSHOT OF [' + @dbname + '];' 
				
				SET @sqlcmd_Drop = 'USE master; 
IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = ''' + @dbname + '_CheckDB_Snapshot'') 
DROP DATABASE [' + @dbname + '_CheckDB_Snapshot];'
			END
			
			IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NULL
			BEGIN
				SET @sqlcmd = NULL
				SELECT @Message = '** Skipping database ' + @dbname + ': Could not find a valid path to create DB snapshot - ' + CONVERT(VARCHAR, GETDATE())
				RAISERROR(@Message, 0, 42) WITH NOWAIT;
			END
			
			IF @HasROFG > 0 AND @SingleUser = 1
			BEGIN
				SET @sqlcmd = 'USE master;
ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + CHAR(10) + @sqlcmd + CHAR(10) + 
'USE master;
ALTER DATABASE [' + @dbname + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
			END
			
				IF @sqlcmd_Create IS NOT NULL
				BEGIN TRY
					SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Creating database snapshot ' + @dbname + '_CheckDB_Snapshot';
					RAISERROR (@msg, 10, 1) WITH NOWAIT	
					EXEC sp_executesql @sqlcmd_Create;
				END TRY
				BEGIN CATCH
					EXEC sp_executesql @sqlcmd_Drop;
					
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Create Snapshot - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
				END CATCH

				IF @sqlcmd IS NOT NULL
				BEGIN TRY					
					EXEC sp_executesql @sqlcmd;

					UPDATE tblFgBuckets
					SET isdone = 1
					FROM tblFgBuckets
					WHERE [database_id] = @dbid AND [data_space_id] = @fg_id AND used_page_count = @used_page_count AND isdone = 0 AND BucketId = @TodayBucket
				END TRY
				BEGIN CATCH					
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Check cycle - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
					RETURN
				END CATCH
				
				IF @sqlcmd_Drop IS NOT NULL
				BEGIN TRY
					SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Droping database snapshot ' + @dbname + '_CheckDB_Snapshot';
					RAISERROR (@msg, 10, 1) WITH NOWAIT	
					EXEC sp_executesql @sqlcmd_Drop;
				END TRY
				BEGIN CATCH
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Drop Snapshot - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
				END CATCH
		END

		IF @VLDBMode = 0 -- Now do table checks on todays bucket
		BEGIN
			WHILE (SELECT COUNT(*) FROM tblDbBuckets WHERE [database_id] = @dbid AND isdone = 0 AND BucketId = @TodayBucket) > 0
			BEGIN
				SELECT TOP 1 @name = [name], @schema = [schema], @used_page_count = used_page_count
				FROM tblDbBuckets
				WHERE [database_id] = @dbid AND isdone = 0 AND BucketId = @TodayBucket
				ORDER BY used_page_count DESC

				SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Started table checks on ' + @dbname + ' - table ' + @schema + '.' + @name;
				RAISERROR (@msg, 10, 1) WITH NOWAIT

				SET @sqlcmd = 'USE [' + @dbname + '];
DBCC CHECKTABLE (''' + @schema + '.' + @name + ''') WITH '

				IF @Physical = 1
				BEGIN
					SET @sqlcmd = @sqlcmd + 'PHYSICAL_ONLY;'
				END
				ELSE
				BEGIN
					SET @sqlcmd = @sqlcmd + 'DATA_PURITY;'
				END;

				IF @sqlcmd IS NOT NULL
				BEGIN TRY
					EXEC sp_executesql @sqlcmd;

					UPDATE tblDbBuckets
					SET isdone = 1
					FROM tblDbBuckets
					WHERE [database_id] = @dbid AND [name] = @name AND [schema] = @schema AND used_page_count = @used_page_count AND isdone = 0 AND BucketId = @TodayBucket
				END TRY
				BEGIN CATCH					
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
				END CATCH
			END
		END

		IF @VLDBMode = 1 -- Now do filegroup checks on todays bucket
		BEGIN
			WHILE (SELECT COUNT(*) FROM tblFgBuckets WHERE [database_id] = @dbid AND isdone = 0 AND BucketId = @TodayBucket) > 0
			BEGIN
				SELECT TOP 1 @fg_id = [data_space_id], @name = [name], @used_page_count = used_page_count
				FROM tblFgBuckets
				WHERE [database_id] = @dbid AND isdone = 0 AND BucketId = @TodayBucket
				ORDER BY used_page_count DESC

				SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Started filegroup checks on [' + @dbname + '] - filegroup ' + @name;
				RAISERROR (@msg, 10, 1) WITH NOWAIT

				IF @HasROFG > 0 AND @CreateSnap = 1
				SET @sqlcmd = 'USE [' + @dbname + '_CheckDB_Snapshot];
DBCC CHECKFILEGROUP (' + CONVERT(NVARCHAR(10), @fg_id) + ')'
				
				IF (@HasROFG > 0 AND @SingleUser = 1) OR (@HasROFG = 0)
				SET @sqlcmd = 'USE [' + @dbname + '];
DBCC CHECKFILEGROUP (' + CONVERT(NVARCHAR(10), @fg_id) + ')'

				IF @Physical = 1
				BEGIN
					SET @sqlcmd = @sqlcmd + ' WITH PHYSICAL_ONLY;'
				END
				ELSE
				BEGIN
					SET @sqlcmd = @sqlcmd + ';'
				END;

				IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NOT NULL
				BEGIN
					TRUNCATE TABLE #tblSnapFiles;
				
					INSERT INTO #tblSnapFiles
					SELECT name, 0 FROM sys.master_files WHERE database_id = @dbid AND [type] = 0;
				
					SET @filecreateid = 1
					SET @sqlsnapcmd = ''

					WHILE (SELECT COUNT([name]) FROM #tblSnapFiles WHERE isdone = 0) > 0
					BEGIN
						SELECT TOP 1 @filename = [name] FROM #tblSnapFiles WHERE isdone = 0
						SET @sqlsnapcmd = @sqlsnapcmd + CHAR(10) + '(NAME = [' + @filename + '], FILENAME = ''' + @SnapPath + '\' + @dbname + '_CheckDB_Snapshot_Data_' + CONVERT(VARCHAR(10), @filecreateid) + '.ss''),'
						SET @filecreateid = @filecreateid + 1

						UPDATE #tblSnapFiles
						SET isdone = 1 WHERE [name] = @filename;
					END;

					SELECT @sqlsnapcmd = LEFT(@sqlsnapcmd, LEN(@sqlsnapcmd)-1);

					SET @sqlcmd_Create = 'USE master;
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = ''' + @dbname + '_CheckDB_Snapshot'')
CREATE DATABASE [' + @dbname + '_CheckDB_Snapshot] ON ' + @sqlsnapcmd + CHAR(10) + 'AS SNAPSHOT OF [' + @dbname + '];' 

					SET @sqlcmd_Drop = 'USE master; 
IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = ''' + @dbname + '_CheckDB_Snapshot'') 
DROP DATABASE [' + @dbname + '_CheckDB_Snapshot];'
				END;
			
				IF @HasROFG > 0 AND @CreateSnap = 1 AND @SnapPath IS NULL
				BEGIN
					SET @sqlcmd = NULL
					SELECT @Message = '** Skipping database ' + @dbname + ': Could not find a valid path to create DB snapshot - ' + CONVERT(VARCHAR, GETDATE())
					RAISERROR(@Message, 0, 42) WITH NOWAIT;
				END
				
				IF @HasROFG > 0 AND @SingleUser = 1
				BEGIN
					SET @sqlcmd = 'USE master;
ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' + CHAR(10) + @sqlcmd + CHAR(10) + 
'USE master;
ALTER DATABASE [' + @dbname + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
				END

				IF @sqlcmd_Create IS NOT NULL
				BEGIN TRY
					SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Creating database snapshot ' + @dbname + '_CheckDB_Snapshot';
					RAISERROR (@msg, 10, 1) WITH NOWAIT	
					EXEC sp_executesql @sqlcmd_Create;
				END TRY
				BEGIN CATCH
					EXEC sp_executesql @sqlcmd_Drop;
					
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Create Snapshot - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
				END CATCH

				IF @sqlcmd IS NOT NULL
				BEGIN TRY					
					EXEC sp_executesql @sqlcmd;

					UPDATE tblFgBuckets
					SET isdone = 1
					FROM tblFgBuckets
					WHERE [database_id] = @dbid AND [data_space_id] = @fg_id AND used_page_count = @used_page_count AND isdone = 0 AND BucketId = @TodayBucket
				END TRY
				BEGIN CATCH					
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Check cycle - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
					RETURN
				END CATCH
				
				IF @sqlcmd_Drop IS NOT NULL
				BEGIN TRY
					SELECT @msg = CONVERT(VARCHAR, GETDATE(), 9) + ' - Droping database snapshot ' + @dbname + '_CheckDB_Snapshot';
					RAISERROR (@msg, 10, 1) WITH NOWAIT	
					EXEC sp_executesql @sqlcmd_Drop;
				END TRY
				BEGIN CATCH
					SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
					SELECT @ErrorMessage = 'Drop Snapshot - Error raised in TRY block. ' + ERROR_MESSAGE()
					RAISERROR (@ErrorMessage, 16, 1);
				END CATCH			END
		END
	END;
 
	UPDATE #tmpdbs
	SET isdone = 1
	FROM #tmpdbs
	WHERE [dbid] = @dbid AND isdone = 0
END;

IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tmpdbs'))
DROP TABLE #tmpdbs
IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblObj'))
DROP TABLE #tblObj;
IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblBuckets'))
DROP TABLE #tblBuckets;
IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblFGs'))
DROP TABLE #tblFGs;
IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#tblSnapFiles'))
DROP TABLE #tblSnapFiles;

SELECT @Message = '** Finished: ' + CONVERT(VARCHAR, GETDATE())
RAISERROR(@Message, 0, 42) WITH NOWAIT;
GO

PRINT 'Procedure usp_CheckIntegrity created';
GO

------------------------------------------------------------------------------------------------------------------------------	

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Weekly Maintenance')
EXEC msdb.dbo.sp_delete_job @job_name=N'Weekly Maintenance', @delete_unused_schedule=1
GO

PRINT 'Creating Weekly Maintenance job';
GO

BEGIN TRANSACTION

-- Set the Operator name to receive notifications, if any. Set the job owner, if not sa.
DECLARE @customoper sysname, @jobowner sysname
SET @customoper = 'SQLAdmins'
SET @jobowner = 'sa'

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
IF EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = @customoper)
BEGIN
	EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Weekly Maintenance', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=3, 
		@notify_level_netsend=2, 
		@notify_level_page=2,  
		@delete_level=0, 
		@description=N'Runs weekly maintenance cycle. Most steps execute on Fridays only. For integrity checks, depending on whether the database in scope is a VLDB or not, different actions are executed. See job steps for further detail.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=@jobowner, 
		@notify_email_operator_name=@customoper, 
		@job_id = @jobId OUTPUT
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
ELSE
BEGIN
	EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Daily Index Defrag', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=3, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Inteligent defrag on one or more indexes for one or more databases.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=@jobowner,
		@job_id = @jobId OUTPUT
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBCC CheckDB', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@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=N'TSQL', 
		@command=N'/* 
This checks the logical and physical integrity of all the objects in the specified database by performing the following operations: 
|-For VLDBs (larger than 1TB):
  |- On Fridays, if VLDB Mode = 0, runs DBCC CHECKALLOC.
  |- On Fridays, runs DBCC CHECKCATALOG.
  |- Everyday, if VLDB Mode = 0, runs DBCC CHECKTABLE or if VLDB Mode = 1, DBCC CHECKFILEGROUP on a subset of tables and views, divided by daily buckets.
|-For DBs smaller than 1TB:
  |- Every Friday a DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database.

To set how VLDBs are handled, set @VLDBMode to 0 = Bucket by Table Size or 1 = Bucket by Filegroup Size

IMPORTANT: Consider running DBCC CHECKDB routinely (at least, weekly). On large databases and for more frequent checks, consider using the PHYSICAL_ONLY parameter.
http://msdn.microsoft.com/en-us/library/ms176064.aspx
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx

If a database has Read-Only filegroups, any integrity check will fail if there are other open connections to the database.

Setting @CreateSnap = 1 will create a database snapshot before running the check on the snapshot, and drop it at the end (default).
Setting @CreateSnap = 0 means the integrity check might fail if there are other open connection on the database.


If snapshots are not allowed and a database has Read-Only filegroups, any integrity check will fail if there are other openned connections to the database.
Setting @SingleUser = 1 will set the database in single user mode before running the check, and to multi user afterwards.
Setting @SingleUser = 0 means the integrity check might fail if there are other open connection on the database.
*/

EXEC msdb.dbo.usp_CheckIntegrity @VLDBMode = 1, @SingleUser = 0, @CreateSnap = 1
', 
		@database_name=N'master', 
		@flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'update usage', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@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=N'TSQL', 
		@command=N'/*
DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index.
IMPORTANT: Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.
http://msdn.microsoft.com/en-us/library/ms188414.aspx

Exludes all Offline or Read-Only DBs. Also excludes all databases over 4GB in size.
*/

SET NOCOUNT ON;
-- Is it Friday yet?
IF (SELECT 32 & POWER(2, DATEPART(weekday, GETDATE())-1)) > 0
BEGIN
	PRINT ''** Start: '' + CONVERT(VARCHAR, GETDATE())
	DECLARE @dbname sysname, @sqlcmd NVARCHAR(500)
	IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(''tempdb.dbo.#tmpdbs''))
	CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbname] sysname, isdone bit)

	INSERT INTO #tmpdbs ([dbname], isdone)
	SELECT QUOTENAME(d.name), 0 FROM sys.databases d INNER JOIN sys.master_files smf ON d.database_id = smf.database_id
	WHERE d.is_read_only = 0 AND d.state = 0 AND d.database_id <> 2 AND smf.type = 0 AND (smf.size * 8)/1024 < 4096;

	WHILE (SELECT COUNT([dbname]) FROM #tmpdbs WHERE isdone = 0) > 0
	BEGIN
		SET @dbname = (SELECT TOP 1 [dbname] FROM #tmpdbs WHERE isdone = 0)
		SET @sqlcmd = ''DBCC UPDATEUSAGE ('' + @dbname + '')''
		PRINT CHAR(10) + CONVERT(VARCHAR, GETDATE()) + '' - Started space corrections on '' + @dbname
		EXECUTE sp_executesql @sqlcmd
		PRINT CONVERT(VARCHAR, GETDATE()) + '' - Ended space corrections on '' + @dbname
			
		UPDATE #tmpdbs
		SET isdone = 1
		FROM #tmpdbs
		WHERE [dbname] = @dbname AND isdone = 0
	END;

	IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(''tempdb.dbo.#tmpdbs''))
	DROP TABLE #tmpdbs;

	PRINT ''** Finished: '' + CONVERT(VARCHAR, GETDATE())
END
ELSE
BEGIN
	PRINT ''** Skipping: Today is not Friday - '' + CONVERT(VARCHAR, GETDATE())
END;', 
		@database_name=N'master', 
		@flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sp_createstats', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@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=N'TSQL', 
		@command=N'/*
Creates statistics only on columns that are part of an existing index, and are not the first column in any index definition. 
Creating single-column statistics increases the number of histograms, which can improve cardinality estimates, query plans, and query performance. 
The first column of a statistics object has a histogram; other columns do not have a histogram. 

http://msdn.microsoft.com/en-us/library/ms186834.aspx

Exludes all Offline and Read-Only DBs
*/

SET NOCOUNT ON;
-- Is it Friday yet?
IF (SELECT 32 & POWER(2, DATEPART(weekday, GETDATE())-1)) > 0
BEGIN
	PRINT ''** Start: '' + CONVERT(VARCHAR, GETDATE())
	DECLARE @dbname sysname, @sqlcmd NVARCHAR(500)

	IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(''tempdb.dbo.#tmpdbs''))
	CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbname] sysname, isdone bit)

	INSERT INTO #tmpdbs ([dbname], isdone)
	SELECT QUOTENAME(name), 0 FROM sys.databases WHERE is_read_only = 0 AND state = 0 AND database_id > 4 AND is_distributor = 0;

	WHILE (SELECT COUNT([dbname]) FROM #tmpdbs WHERE isdone = 0) > 0
	BEGIN
		SET @dbname = (SELECT TOP 1 [dbname] FROM #tmpdbs WHERE isdone = 0)
		SET @sqlcmd = @dbname + ''.dbo.sp_createstats @indexonly = ''''indexonly''''''
		SELECT CHAR(10) + CONVERT(VARCHAR, GETDATE()) + '' - Started indexed stats creation on '' + @dbname
		EXECUTE sp_executesql @sqlcmd
		SELECT CONVERT(VARCHAR, GETDATE()) + '' - Ended indexed stats creation on '' + @dbname

		UPDATE #tmpdbs
		SET isdone = 1
		FROM #tmpdbs
		WHERE [dbname] = @dbname AND isdone = 0
	END;

	IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(''tempdb.dbo.#tmpdbs''))
	DROP TABLE #tmpdbs;
	PRINT ''** Finished: '' + CONVERT(VARCHAR, GETDATE())
END
ELSE
BEGIN
	PRINT ''** Skipping: Today is not Friday - '' + CONVERT(VARCHAR, GETDATE())
END;', 
		@database_name=N'master', 
		@flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cleanup Job History', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@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=N'TSQL', 
		@command=N'-- Cleans msdb job history older than 30 days
SET NOCOUNT ON;
-- Is it Friday yet?
IF (SELECT 32 & POWER(2, DATEPART(weekday, GETDATE())-1)) > 0
BEGIN
	DECLARE @date DATETIME
	SET @date = GETDATE()-30
	EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@date;
END
ELSE
BEGIN
	PRINT ''** Skipping: Today is not Friday - '' + CONVERT(VARCHAR, GETDATE())
END;', 
		@database_name=N'msdb', 
		@flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cleanup Maintenance Plan txt reports', 
		@step_id=5, 
		@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=N'TSQL', 
		@command=N'-- Cleans maintenance plans txt reports older than 30 days
SET NOCOUNT ON;
-- Is it Friday yet?
IF (SELECT 32 & POWER(2, DATEPART(weekday, GETDATE())-1)) > 0
BEGIN
	DECLARE @path NVARCHAR(500), @date DATETIME
	DECLARE @sqlcmd NVARCHAR(1000), @params NVARCHAR(100), @sqlmajorver int

	SELECT @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);
	SET @date = GETDATE()-30

	IF @sqlmajorver < 11
	BEGIN
		EXEC master..xp_instance_regread N''HKEY_LOCAL_MACHINE'',N''Software\Microsoft\MSSQLServer\Setup'',N''SQLPath'', @path OUTPUT
		SET @path = @path + ''\LOG''
	END
	ELSE
	BEGIN
		SET @sqlcmd = N''SELECT @pathOUT = LEFT([path], LEN([path])-1) FROM sys.dm_os_server_diagnostics_log_configurations'';
		SET @params = N''@pathOUT NVARCHAR(2048) OUTPUT'';
		EXECUTE sp_executesql @sqlcmd, @params, @pathOUT=@path OUTPUT;
	END

	-- Default location for maintenance plan txt files is the Log folder. 
	-- If you changed from the default location since you last installed SQL Server, uncomment below and set the custom desired path.
	--SET @path = ''C:\custom_location''

	EXECUTE master..xp_delete_file 1,@path,N''txt'',@date,1
END
ELSE
BEGIN
	PRINT ''** Skipping: Today is not Friday - '' + CONVERT(VARCHAR, GETDATE())
END;', 
		@database_name=N'master', 
		@flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly Maintenance - Fridays', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20071009, 
		@active_end_date=99991231, 
		@active_start_time=83000, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly Maintenance - Weekdays and Saturdays', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=126, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20131017, 
		@active_end_date=99991231, 
		@active_start_time=10000, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

PRINT 'Weekly Maintenance job created';
GO