T-SQL Solution:
/*EXECUTE DBCC CHECKDB FOR EACH DATABASE IN AN INSTANCE*/
Declare @dbname varchar(250), @dbid int = 1, @maxdbid int, @Debug int = 1
Select @maxdbid = Max(dbid) from master..sysdatabases
While @dbid <= @maxdbid
Begin
Set @dbname = ''
Select @dbname = name from master..sysdatabases where [dbid] = @dbid
IF @dbname is not null and @dbname <> ''
Begin
Print CHAR(10);
Print '>>> Starting DBCC CHECKDB: ' + UPPER(@dbname) + ' at ' + Convert(varchar(25),Getdate()) + '...' + CHAR(10);
DBCC CHECKDB(@dbname) WITH NO_INFOMSGS;
Print CHAR(10);
Print '>>> Finised DBCC CHECKDB: ' + UPPER(@dbname) + ' at ' + Convert(varchar(25),Getdate()) + '...' + CHAR(10);
Set @dbid = @dbid + 1
End
End;
/*LOAD LATEST SQL SERVER ERROR LOG INTO TEMP TABLE TO GET ALL DBCC CHECKDB ERRORS*/
/*Sample Error Log entry: DBCC CHECKDB ... found 0 errors and repaired 0 errors.*/
CREATE TABLE #TMP_ERROR_LOG ([SQLServerLogDate] datetime, [SourceInformation] varchar(100), [Message] varchar(500))
INSERT INTO #TMP_ERROR_LOG EXEC sp_readerrorlog 0, 1, 'DBCC CHECKDB'
/*PARSE DBCC CHECKDB FOUND ERROR AND REPAIRED ERROR VALUES*/
Declare @str1 varchar(10) = 'found'
Declare @str2 varchar(10) = 'errors'
Declare @str3 varchar(10) = 'repaired'
Declare @str4 varchar(10) = 'errors.'
Declare @str1len int = LEN(@str1)
Declare @str2len int = LEN(@str2)
Declare @str3len int = LEN(@str3)
Declare @str4len int = LEN(@str3)
Declare @MaxSQLServerLogDate datetime
Set @MaxSQLServerLogDate = (Select Max([SQLServerLogDate]) from dbo.LOG_DBCC_CHECKDB)
/*LOG TO PERMANENT TABLE*/
Insert into dbo.LOG_DBCC_CHECKDB ([SQLServerLogDate], [Database], [FoundErrors], [RepairedErrors], [SourceInformation], [InsertedDate])
Select
[SQLServerLogDate]
,UPPER(RTRIM(LTRIM(SUBSTRING([Message], CHARINDEX('(', [Message]) + 1, CHARINDEX(')', [Message]) - 1 - CHARINDEX('(', [Message]))))) as 'Database'
,RTRIM(LTRIM(SUBSTRING([Message], CHARINDEX('found', [Message]) + @str1len, CHARINDEX('errors', [Message]) - @str1len - CHARINDEX('found', [Message])))) as 'Found Errors'
,RTRIM(LTRIM(SUBSTRING([Message], CHARINDEX('repaired', [Message]) + @str3len, CHARINDEX('errors.', [Message]) - @str3len - CHARINDEX('repaired', [Message])))) as 'Repaired Errors'
,[SourceInformation]
, GetDate()
From #TMP_ERROR_LOG
Where [SQLServerLogDate] > (Select ISNULL(Max([SQLServerLogDate]), '') from dbo.LOG_DBCC_CHECKDB)
order by 1 DESC
/*RAISEERROR IF LATEST DBCC CHECKDB CONTAIN ERROR(s)*/
If Exists (Select 1 from dbo.LOG_DBCC_CHECKDB Where [FoundErrors] <> 0 or [RepairedErrors] <> 0 and [SQLServerLogDate] > @MaxSQLServerLogDate)
Begin
RAISERROR('DBCC CHECKDB Error(s)! See dbo.LOG_DBCC_CHECKDB table for details.', 16, 1)
End
Else
Begin
Select 'DBCC CHECKDB has [ 0 ] errors in most recent execution.' as 'Message'
End
/*DEBUG MODE ON*/
If @Debug=1 Begin
SELECT SUM([FoundErrors]) as 'Total Found Errors', SUM([RepairedErrors]) as 'Total Repair Errors' from dbo.LOG_DBCC_CHECKDB
SELECT * from dbo.LOG_DBCC_CHECKDB order by LogID desc
End
Drop Table #TMP_ERROR_LOG
--CREATE TABLE dbo.LOG_DBCC_CHECKDB ([LogID] int Identity(1,1), [SQLServerLogDate] datetime, [Database] varchar(100), [FoundErrors] int, [RepairedErrors] int, [SourceInformation] varchar(100), [InsertedDate] datetime)
|
|
|
|
|
|