Automatically DBCC CHECKDB your databases

20 Aug 2019 0 minutes to read Contributors


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Functionality
=============
DBCC CHECKDB all databases

Applications
============
--

PRERequisites
=============
- A Table containing servernames and database names. (This table will be used to restore the backup's of those databases onto a server to offload
the DBCC CHECKDB
- usp_restoredbV5 - A custom procedure to automatically restore databases.
- Ola Halengren's [DatabaseIntegrityCheck]

VERSION HISTORY
Version Date Author Reason
------------------------------------------------------------------------------
1.0 20160520 drb creation

Logging
=======
local Table TargetDatabases

Results
=======
TargetDatabases and Ola's Commandlog
*/
CREATE procedure [dbo].[usp_dba_dbcc_checkdatabases]
as
begin
SET NOCOUNT ON
DECLARE @id int,
@starttime datetime,
@endtime datetime,
@OnlyDoThisOnce tinyint = 1,
@dbcc_errorcode int,
@result varchar(max)

-- Parameters for usp_restoredb proc
DECLARE @server sysname,
@database sysname,
@restore_to sysname ='@@SERVERNAME WHERE YOU WANT THE BACKUP TO BE RESTORED TO',
@restore_as sysname,
@restore_data_to sysname = 'I:\DBCC_CHECKDB_FOLDER\',
@restore_log_to sysname = 'T:\DBCC_CHECKDB_FOLDER\',
@immediate_restore bit = 1,
@username sysname = NULL,
@password sysname = NULL

IF OBJECT_ID( 'TargetDatabases' ) = 0
BEGIN
EXEC('CREATE TABLE TargetDatabases( id int identity(1,1) PRIMARY KEY, servername sysname,recovery varchar(20) NULL,database_name sysname NULL, State sysname NULL, duration int null, dbcc_checked datetime null, dbcc_error bit default 0, message varchar(150) null, uid sysname, pwd sysname)')
END

WHILE( 1=1 )
BEGIN
-- See if we're in the weekend..., if not... Stop dbcc
-- IF( DATENAME(WEEKDAY, current_timestamp ) NOT IN ('Saturday','Sunday') )
-- BREAK

-- If we have nothing to do, fetch a new list of DB's
IF NOT EXISTS( SELECT 1 FROM TargetDatabases where dbcc_checked IS NULL) AND @OnlyDoThisOnce = 1
BEGIN
SET @OnlyDoThisOnce = 2

insert into TargetDatabases( servername, recovery, database_name, state, UID, PWD )
select servername, recovery, database_name, state, uid, pwd from OPENROWSET('SQLNCLI11','DRIVER={SQL Server};Server=@@SERVERNAME_WHERE_TARGETDATABASES_IS_LOCATED;Database=@@DATABASE;Persist Security Info=True;UID=@@READONLY_LOGIN;PWD=@@READONLY_PASSWORD','select * from targetdatabases')

-- Don't check any Test or Uat DB's and don't check SDW itself also
DELETE FROM TargetDatabases where dbcc_checked is null and servername like '@@EXCLUDESERVERS%' OR servername like '@@EXCLUDESERVERS%' OR
servername IN ( @@EXCLUDESERVERS )

-- Check what we do not have to check again
UPDATE newbatch
SET dbcc_checked = CURRENT_TIMESTAMP,
message = 'Already checked in the last 24 hours'
FROM TargetDatabases oldbatch LEFT OUTER JOIN
TargetDatabases newbatch ON DATEDIFF( hour, oldbatch.dbcc_checked, current_timestamp ) < 28 and oldbatch.dbcc_error = 0 and newbatch.dbcc_checked IS NULL and newbatch.servername=oldbatch.servername and newbatch.database_name=oldbatch.database_name
WHERE newbatch.id IS NOT NULL
END

-- This is to eliminate any newly added databases to SDW during a batch of DBCC check
DELETE FROM TargetDatabases where dbcc_checked IS NULL and database_name IN (select name from sysdatabases where name not in ('master','msdb','distribution'))

-- Fetch the next DB to be processed
SELECT TOP 1 @id = id, @server = servername, @database = database_name, @restore_as = database_name+'_dbcc', @username=UID, @password=PWD FROM TargetDatabases where dbcc_checked IS NULL

-- If we have processed all, yeah right!
IF(@@ROWCOUNT = 0 )
BREAK

-- update status
UPDATE TargetDatabases
SET message = 'Restoring database...',
dbcc_checked = CURRENT_TIMESTAMP
WHERE ID=@id

RAISERROR( 'Working on %s:%i',1,0, @database,@id )

SET @starttime = CURRENT_TIMESTAMP

BEGIN TRY
EXEC usp_restoredbV5 @server = @server, @database = @database, @restore_to = @restore_to, @restore_as = @restore_as, @restore_data_to= @restore_data_to, @restore_log_to=@restore_log_to, @immediate_restore=@immediate_restore, @username=@username, @password=@password, @result = @result OUTPUT

IF DATABASEPROPERTYEX( @restore_as, 'Status' ) IN ('OFFLINE', 'RESTORING', 'RECOVERING', 'SUSPECT', 'EMERGENCY' ) OR DATABASEPROPERTYEX( @restore_as, 'Updateability' ) = 'READ_ONLY'
BEGIN
UPDATE TargetDatabases
SET message = 'Database in error recovery :'+CAST(DATABASEPROPERTYEX( @restore_as, 'Status' ) as varchar(10))
WHERE ID=@id

EXEC( 'DROP DATABASE ['+@database+'_dbcc]')
END
ELSE
IF DATABASEPROPERTYEX( @restore_as, 'Status' ) = NULL
BEGIN
UPDATE TargetDatabases
SET message = 'Could not find database'
WHERE ID=@id
END
ELSE
BEGIN
-- update status
UPDATE TargetDatabases
SET message = 'Database restored, doing a DatabaseIntegrityCheck'
WHERE ID=@id


-- OLA HALENGREN
exec @dbcc_errorcode = [DatabaseIntegrityCheck]
@Databases = @restore_as,
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'N',
@NoIndex = 'N',
@ExtendedLogicalChecks = 'N',
@TabLock = 'Y',
@FileGroups = NULL,
@Objects = NULL,
@LockTimeout = NULL,
@LogToTable = 'Y',
@Execute = 'Y'

EXEC( 'DROP DATABASE ['+@database+'_dbcc]')

SET @endtime = CURRENT_TIMESTAMP

-- update status
IF( @dbcc_errorcode = 0 )
BEGIN
UPDATE TargetDatabases SET duration = DATEDIFF( second, @starttime, @endtime ), dbcc_error = 0, message = 'Finished succesfully without errors.'
WHERE ID=@id
END
ELSE
BEGIN
UPDATE TargetDatabases SET duration = DATEDIFF( second, @starttime, @endtime ),dbcc_error = @dbcc_errorcode, message = 'Finished but WITH DBCC ERRORS.'
WHERE ID=@id
END
END
END TRY
BEGIN CATCH
DECLARE @error varchar(max) = ERROR_MESSAGE()+' - '+ISNULL(@result,'')
-- update status
UPDATE TargetDatabases SET message = @error
WHERE ID=@id

-- DROP THE DBCC RESTORED DATABASE
EXEC( 'DROP DATABASE ['+@database+'_dbcc]')

END CATCH
END
end

In this article