Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Script to quickly find SQL Server DBCC CHECKDB Errors

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


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.


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;

CREATE FUNCTION dbo.GetStringBetween (@str varchar(500), @str1 varchar(30), @str2 varchar(30))
RETURNS varchar(200)
   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  )))

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;

CREATE PROCEDURE dbo.usp_check_dbcccheckdbresults
   CREATE TABLE #tempLogRes 
      (LogDate datetime,
       ProcessInfo varchar(100),
       TextLine varchar(500))

   INSERT #tempLogRes EXEC sp_readerrorlog 0, 1, 'DBCC CHECKDB'

      dbo.GetStringBetween (TextLine ,'(',')' ) AS CHECKED_DB,
      dbo.GetStringBetween (TextLine ,'found','errors' ) AS ERRORS_FOUND,
      dbo.GetStringBetween (TextLine ,'repaired','errors.' ) AS ERRORS_REPAIRED
      TextLine like '%DBCC CHECKDB%' 
      AND LogDate > GETDATE() - 1

   DROP TABLE #tempLogRes

Example Run

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

use master 
exec dbo.usp_check_dbcccheckdbresults

The result for the query is as follows:

-------                   ----------   ------------   ---------------
2017-02-21 12:12:55.550   Northwind    0              0
2017-02-21 14:14:20.220   test         0              0


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.


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. 


Learn more about SQL Server tools