- Docs
- /
06 Jun 2022 16058 views 0 minutes to read Contributors
T-SQLTransact-SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176 SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*Functionality=============DBCC CHECKDB all databasesApplications============--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 offloadthe DBCC CHECKDB- usp_restoredbV5 - A custom procedure to automatically restore databases.- Ola Halengren's [DatabaseIntegrityCheck]VERSION HISTORYVersion Date Author Reason------------------------------------------------------------------------------1.0 20160520 drb creationLogging=======local Table TargetDatabasesResults=======TargetDatabases and Ola's Commandlog*/CREATE procedure [dbo].[usp_dba_dbcc_checkdatabases]asbeginSET NOCOUNT ONDECLARE @id int,@starttime datetime,@endtime datetime,@OnlyDoThisOnce bit= 1,@dbcc_errorcode int,@result varchar(max)-- Parameters for usp_restoredb procDECLARE @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 = NULLIF OBJECT_ID( 'TargetDatabases' ) IS NULLBEGINEXEC('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)')ENDWHILE( 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'sIF NOT EXISTS( SELECT 1 FROM TargetDatabases where dbcc_checked IS NULL) AND @OnlyDoThisOnce = 1BEGINSET @OnlyDoThisOnce = 2insert 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 alsoDELETE FROM TargetDatabases where dbcc_checked is null and servername like '@@EXCLUDESERVERS%' OR servername like '@@EXCLUDESERVERS%' ORservername IN ( @@EXCLUDESERVERS )-- Check what we do not have to check againUPDATE newbatchSET dbcc_checked = CURRENT_TIMESTAMP,message = 'Already checked in the last 24 hours'FROM TargetDatabases oldbatch LEFT OUTER JOINTargetDatabases 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_nameWHERE newbatch.id IS NOT NULLEND-- This is to eliminate any newly added databases to SDW during a batch of DBCC checkDELETE 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 processedSELECT 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 statusUPDATE TargetDatabasesSET message = 'Restoring database...',dbcc_checked = CURRENT_TIMESTAMPWHERE ID=@idRAISERROR( 'Working on %s:%i',1,0, @database,@id )SET @starttime = CURRENT_TIMESTAMPBEGIN TRYEXEC 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 OUTPUTIF DATABASEPROPERTYEX( @restore_as, 'Status' ) IN ('OFFLINE', 'RESTORING', 'RECOVERING', 'SUSPECT', 'EMERGENCY' ) OR DATABASEPROPERTYEX( @restore_as, 'Updateability' ) = 'READ_ONLY'BEGINUPDATE TargetDatabasesSET message = 'Database in error recovery :'+CAST(DATABASEPROPERTYEX( @restore_as, 'Status' ) as varchar(10))WHERE ID=@idEXEC( 'DROP DATABASE ['+@database+'_dbcc]')ENDELSEIF DATABASEPROPERTYEX( @restore_as, 'Status' ) = NULLBEGINUPDATE TargetDatabasesSET message = 'Could not find database'WHERE ID=@idENDELSEBEGIN-- update statusUPDATE TargetDatabasesSET message = 'Database restored, doing a DatabaseIntegrityCheck'WHERE ID=@id-- OLA HALENGRENexec @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 statusIF( @dbcc_errorcode = 0 )BEGINUPDATE TargetDatabases SET duration = DATEDIFF( second, @starttime, @endtime ), dbcc_error = 0, message = 'Finished succesfully without errors.'WHERE ID=@idENDELSEBEGINUPDATE TargetDatabases SET duration = DATEDIFF( second, @starttime, @endtime ),dbcc_error = @dbcc_errorcode, message = 'Finished but WITH DBCC ERRORS.'WHERE ID=@idENDENDEND TRYBEGIN CATCHDECLARE @error varchar(max) = ERROR_MESSAGE()+' - '+ISNULL(@result,'')-- update statusUPDATE TargetDatabases SET message = @errorWHERE ID=@id-- DROP THE DBCC RESTORED DATABASEEXEC( 'DROP DATABASE ['+@database+'_dbcc]')END CATCHENDend
In this article
Please choose a monitoring environment to connect to:
Enter your email address to recover your password.
Download link successfully sent. Check your email for details.
Reset password link successfully sent to . Check your email for details.
An email with a link for creating your password is sent to
Share link successfully sent to .
Your reply is send.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
We received your request. We will contact you shortly