My CheckDB Script

Download PDF

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





Tagged with: , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.