Script to quickly find SQL Server DBCC CHECKDB Errors

Problem

Running DBCC CHECKDBs is something that all DBAs should do, but checking the output for errors can be tedious unless you build a process to identify when deeper investigation needs to be done.  In this tip we look at a function and a stored procedure that can be used to scan the SQL Server error files for any DBCC CHECKDB issues that need to be addressed.

Solution

My solution is based on a stored procedure (dbo.usp_check_dbcccheckdbresults), that queries the SQL Server Error Logs for certain output from DBCC CHECKDB. The procedure uses a user defined function (dbo.GetStringBetween) that gets a string sequence that exists between two strings to find what we are looking for in the Error Log.

SQL Function

The function takes two strings such ‘Str1’ and ‘Str2’ and returns anything in the middle of these strings.

So if we pass the following @str = ‘…..Str1ccccccStr2……’, @str1 = ‘Str1’ and @str2 = ‘Str2’ the function will return ‘cccccc’ (the characters between ‘Str1’ and ‘Str2’).

USE master;
GO
CREATE FUNCTION dbo.GetStringBetween (@str varchar(500), @str1 varchar(30), @str2 varchar(30))
RETURNS varchar(200)
AS
BEGIN
   DECLARE @Result varchar(200)
   DECLARE @p1 int
   DECLARE @p2 int
   SET @p1 = charindex (@str1 , @str ,1)
   SET @p2 = charindex (@str2 , @str ,1)
   RETURN rtrim(ltrim(substring (@str, @p1 + len(@str1) , @p2 - len(@str1) - @p1  )))
END
GO

SQL Stored Procedure

The method:

1. Each DBCC CHECKDB statement logs a line to the current SQL Server Error Log (current log).
2. This line states that DBCC CHECKDB was executed on database (some database) and Found nnn errors Repaired nnn errors and took … time to finish.
3. The procedure will use sp_readerrorlog to read the error log file messages into a temporary table.
4. Using the function we get the following details
   1. The string between the parentheses is the database name.
   2. The string between the words ‘found’ and ‘errors’ is the number or errors found
   3. The string between the words ‘repaired’ and ‘errors’ is the number of errors repaired.

If the number of errors found equals zero or if the number of errors found equals the number of errors repaired, then DBCC CHECKDB worked OK. You should investigate the database if the errors numbers are greater than zero or if the number of errors repaired is less than number or errors found.

USE master;
GO
CREATE PROCEDURE dbo.usp_check_dbcccheckdbresults
as
BEGIN
   SET NOCOUNT ON
   
   CREATE TABLE #tempLogRes 
      (LogDate datetime,
       ProcessInfo varchar(100),
       TextLine varchar(500))
   INSERT #tempLogRes EXEC sp_readerrorlog 0, 1, 'DBCC CHECKDB'
   SELECT 
      LogDate,
      dbo.GetStringBetween (TextLine ,'(',')' ) AS CHECKED_DB,
      dbo.GetStringBetween (TextLine ,'found','errors' ) AS ERRORS_FOUND,
      dbo.GetStringBetween (TextLine ,'repaired','errors.' ) AS ERRORS_REPAIRED
   FROM 
      #tempLogRes 
   WHERE 
      TextLine like '%DBCC CHECKDB%' 
      AND LogDate > GETDATE() - 1
   DROP TABLE #tempLogRes
  
   SET NOCOUNT OFF
END
GO

Example Run

This is how you run the stored procedure after the stored procedure and function have been created.

use master 
go 
exec dbo.usp_check_dbcccheckdbresults
go 

The result for the query is as follows:

LogDate                   CHECKED_DB   ERRORS_FOUND   ERRORS_REPAIRED
-------                   ----------   ------------   ---------------
2017-02-21 12:12:55.550   Northwind    0              0
2017-02-21 14:14:20.220   test         0              0
 

Notes

The procedure was tested with SQL Server 2012 and SQL Server 2014 Developer Editions.

This procedure should be executed each day after your DBCC CHECKDB job finishes and the output should be reviewed.

Next Steps

• Modify the process to include a different date range
• Modify the process to limit which databases are checked
• Write results to a history table
• Modify process to send a notification email if there are errors

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

• MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019