Spread out Integrity Checks across all 7 days of week

[coltengoll]

Several of my clients have large databases that need to have their own integrity check job. If we ran the integrity checks against all databases, it would run for longer than a day.

I've written some code that wraps the EXEC dbo.DataqbaseIntegrityCheck. It assigns each database a day of the week to run order by size, then tries to have the largest databases run on the weekends.

If the code is deemed desireable, I can integrate into the job setup scripts.

DECLARE @date DATE = DATEADD(DAY,0,GETDATE())
DECLARE @dayOfWeek INT = DATEPART(WEEKDAY,@date)
DECLARE @databaseNames VARCHAR(1000)

;WITH cte AS (
    --Let's break down this query
        --We first order the databases based on size.  That way we try to prevent the largest sizes from being assigned the same day.
        --We then want to perform a modulus (also called mod or remainder) function, so we can easily assign each database a day of the week.
        --Howerver, we add 4 to it, on the inside of the modulus, to manipulate getting the largest database to run Friday night.  This should be 1 + 5 = 6, but we are adding one on the outside, so just 4.
        --Finally, we have to add 1 after all of that to make it 1 based, rather than 0 based.  There is no 0 day of week.
    SELECT d.database_id, d.name, SUM(f.size) AS dbSize, ROW_NUMBER() OVER(ORDER BY SUM(f.size) DESC) AS rowNumber
        ,((ROW_NUMBER() OVER(ORDER BY SUM(f.size) DESC) + 4) % 7) + 1 AS DayOfWeekAssigned
    FROM sys.databases d
    LEFT JOIN sys.master_files f
        ON d.database_id = f.database_id
    WHERE d.name NOT IN('master','model','msdb','tempdb','distribution')
    GROUP BY d.database_id, d.name
)
SELECT @databaseNames = ISNULL(@databaseNames + ', ','') + name
FROM cte
WHERE cte.DayOfWeekAssigned = @dayOfWeek

IF @databaseNames IS NOT NULL
BEGIN
    EXECUTE [dbo].[DatabaseIntegrityCheck]
         @Databases = @databaseNames,
         @LogToTable = 'Y'
END

[Protiguous]

Just a little streamlining.

DECLARE @date DATE = GETDATE();
DECLARE @dayOfWeek INT = DATEPART(WEEKDAY,@date);
DECLARE @databaseNames NVARCHAR(1000) = null;

WITH cte AS (
    /*We first order the databases based on size to prevent the largest sizes from being assigned the same day.*/
    /*We then want to perform a modulus function, so we can assign each database a day of the week.*/
    /*However, we add 4 to it, on the inside of the modulus to manipulate getting the largest database to run Friday night. This should be 1 + 5 = 6, but we are adding one on the outside, so just 4.*/
    /*Finally, we have to add 1 to make it 1 based.*/
    SELECT
        quotename(d.[name]) [name],
        ((ROW_NUMBER() OVER(ORDER BY SUM(f.[size]) DESC) + 4) % 7) + 1 [DayOfWeekAssigned]
    FROM sys.databases d 
    JOIN sys.master_files f ON d.[database_id] = f.[database_id] 
    WHERE NOT d.[name] IN (N'master', N'model', N'msdb', N'tempdb', N'distribution')
    GROUP BY d.[name]
)
SELECT @databaseNames = string_agg(cte.[name], ',')
FROM cte
WHERE cte.[DayOfWeekAssigned] = @dayOfWeek;

IF @databaseNames IS NOT NULL EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = @databaseNames, @LogToTable = 'Y';