By: Eli Leiba | Updated: 2017-02-28 | Comments (3) | 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.
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
- Each DBCC CHECKDB statement logs a line to the current SQL Server Error Log (current log).
- This line states that DBCC CHECKDB was executed on database (some database) and Found nnn errors Repaired nnn errors and took ... time to finish.
- The procedure will use sp_readerrorlog to read the error log file messages into a temporary table.
- Using the function we get the following details
- The string between the parentheses is the database name.
- The string between the words 'found' and 'errors' is the number or errors found
- 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
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
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.
- 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
About the author
View all my tips