Script to quickly find SQL Server DBCC CHECKDB Errors


By:   |   Updated: 2017-02-28   |   Comments (4)   |   Related: More > Database Console Commands DBCCs

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


Last Updated: 2017-02-28


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, October 23, 2019 - 3:40:51 PM - Ilya Bazarsky Back To Top

Hi Eli,

Sorry, I tweaked your procedure a bit. I don’t know how in SQL SERVER 2012 and 2014, but I trying to check in SQL 2016 Standard Edition and I got an error, because in the log is also a line that was ran for check, like DBCC CHECKDB (<DB>, NOINDEX) WITH NO_INFOMSGS, ALL_ERRORMSGS which not contains info about errors. It's happend when DBCC catch an error(s). At the end I also added column with source DBCC command line.

Thank you for your great job! It's very usefull for me. 

 

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,

      RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(dbo.GetStringBetween (TextLine ,'(',')' ), 'NOINDEX', ''), 'REPAIR_ALLOW_DATA_LOSS', ''), 'REPAIR_FAST', ''), 'REPAIR_REBUILD', ''), ',', ''))) AS CHECKED_DB,

      dbo.GetStringBetween (TextLine ,'found','errors' ) AS ERRORS_FOUND,

      dbo.GetStringBetween (TextLine ,'repaired','errors.' ) AS ERRORS_REPAIRED,

      LEFT(TextLine,CHARINDEX('executed by', TextLine) -1) AS STRING_THAT_RAN

   FROM 

      #tempLogRes 

   WHERE 

      TextLine like '%DBCC CHECKDB%' AND TextLine LIKE '%executed by%'

      AND LogDate > GETDATE() - 1

 

   DROP TABLE #tempLogRes

  

   SET NOCOUNT OFF

END

GO


Tuesday, March 27, 2018 - 2:36:35 PM - Gene Torres Back To Top

 How about adding "Recovery Complete" messages as well.  I think it would be good to add.

 


Tuesday, February 28, 2017 - 4:14:26 PM - Sean McCown Back To Top

 Yeah, getting the results from a checkdb has always been difficult.  Have you tried Minion CheckDB?  It keeps all the results in a log table so it's easy to query.

 


Tuesday, February 28, 2017 - 11:27:57 AM - Fraz Back To Top

Hi Eli, Thanks for sharing your excellent work with the group. It seems very useful. 

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools