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!
Leave a Reply