Automatically DBCC CHECKDB your databases

26 Oct 2022 13920 views 0 minutes to read Contributors

		
			
T-SQL
			
Transact-SQL
			

			
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 bit= 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' ) IS NULL
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
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
				
						
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 bit= 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' ) IS NULL
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