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

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.



    



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