My CheckDB Script

From time to time I get asked about checkDB, and there are many solutions out there, but I have one that I generally use that is very simple and does the job.

The script below created a stored procedure in the DBHealthHistory database that can be used to check as many databases as you can get through in a specific time interval. Here it is set to 5 minutes, but that usually gets extended for large databases.  If you set the job to daily, and the job doesn’t get through checking all the databases today, it will pick up where it left off and check the rest tomorrow.

Depending on the number and size of your databases you may want to run this more than once a day, or for a longer period than the 5 minutes.

Note: the 5 minute limitation is checked before starting the next check, so if you have a database that takes hours to check, that will kick off in the 5 minute interval and run until completion.

USE [DBHealthHistory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DatabaseHealthCheckDBAllDatabases]
(
@dontCheckDbDuringBackup INTEGER = 1
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @runUntilTime as DATETIME;
DECLARE @databaseListCursor as CURSOR;
DECLARE @databaseName as NVARCHAR(500);
DECLARE @tsql AS NVARCHAR(500);
DECLARE @Name VARCHAR(255);

SELECT @runUntilTime = DATEADD(MINUTE, 5, GETDATE());

DBCC TRACEON (3604) WITH NO_INFOMSGS;

CREATE TABLE #temp (
Id INT IDENTITY(1,1),
ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255)
);

CREATE TABLE #Results (
DBName VARCHAR(255),
LastGoodDBCC VARCHAR(255)
);

DECLARE dbccLoopingCursor CURSOR
FOR
SELECT name
FROM sys.databases
WHERE CONVERT(varchar(500),databasepropertyex(name, 'Status'),0) = 'ONLINE'
AND is_read_only = 0;

OPEN dbccLoopingCursor
FETCH NEXT FROM dbccLoopingCursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #temp;
INSERT INTO #temp
EXECUTE('DBCC PAGE (['+@Name+'], 1, 9, 3) WITH TABLERESULTS, NO_INFOMSGS');

INSERT INTO #Results
SELECT @Name, MAX(Value) FROM #temp
WHERE Field = 'dbi_dbccLastKnownGood';

FETCH NEXT FROM dbccLoopingCursor INTO @Name
END
CLOSE dbccLoopingCursor;
DEALLOCATE dbccLoopingCursor;

DROP TABLE #temp;

SET @databaseListCursor = CURSOR  LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
WITH ntileCTE AS
(
SELECT DBName,
LastGoodDBCC,
NTILE(10) OVER(ORDER BY LastGoodDBCC) as PercentileRank
FROM #Results
)
SELECT QUOTENAME(DBName)
FROM ntileCTE
WHERE PercentileRank < 5
ORDER BY DATEADD(Day, DATEDIFF(Day, 0, LastGoodDBCC), 0), newid();

OPEN @databaseListCursor;
FETCH NEXT FROM @databaseListCursor into @databaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
IF GETDATE() < @runUntilTime
BEGIN
IF NOT EXISTS (SELECT * FROM master.dbo.sysprocesses WHERE dbid = db_id(@databaseName) AND cmd LIKE 'BACKUP[ ]%DATABASE%')
OR (@dontCheckDbDuringBackup = 0)
BEGIN
SET @tsql = N'DBCC CHECKDB(' + @databaseName + ') WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS;';
--SELECT @tsql;
EXECUTE sp_executesql @tsql;
WAITFOR DELAY '00:00:10';
END
END
FETCH NEXT FROM @databaseListCursor into @databaseName;
END
CLOSE @databaseListCursor;
DEALLOCATE @databaseListCursor;

DROP TABLE #Results;
END

GO

USE [msdb]
GO

BEGIN TRANSACTION
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)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DatabaseHealth-CheckDBAllDatabases',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Runs CheckDB against all databases.

Created by Steve Stedman of Stedman Solutions, LLC, offering remote DBA Services.

For more information visit http://StedmanSolutions.com

Contact Steve at Steve@StedmanSolutions.com, or phone Steve at (360)610-7833.

For specific details on the Database Health Maintenance plans, visit http://DatabaseHealth.com/MaintenancePlansAndJobs',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CheckDB',
@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=N'TSQL',
@command=N'EXECUTE [DBHealthHistory].[dbo].[DatabaseHealthCheckDBAllDatabases] ;',
@database_name=N'DBHealthHistory',
@flags=4
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'DatabaseHealth-CheckDBAllDatabases',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20151012,
@active_end_date=99991231,
@active_start_time=193000, -- The Start Time
@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

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!