When it comes to our health, we rely on doctors to diagnose and treat any issues we may have. Similarly, in the world of SQL Server, it is crucial for database administrators (DBAs) to ensure the consistency of their databases. Just like a doctor looks for patterns and anomalies to identify the cause of an illness, DBAs can use the DBCC commands in SQL Server to check the consistency of their databases.

Many DBAs often wonder how they can determine if their databases are consistent and how to interpret the output of the DBCC CHECKDB command. The key indicator of a consistent database is the absence of allocation errors and consistency errors. When running the CHECKDB command, if you see the following line at the bottom of the output, it is a good sign:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DatabaseName'.

If you encounter non-zero errors, it indicates that there is a problem with the database. The output will provide repair options based on the level of corruption detected. However, it is important to identify the root cause of the problem before proceeding with any repairs.

In addition to running the CHECKDB command, SQL Server provides a standard report called “Database Consistency History” that displays the history of CHECKDB executions for a selected database. This report is generated from the default trace. If the default trace is disabled or there is no record of CHECKDB executions, the report will indicate that no execution history is available.

To automate the generation of the “Database Consistency History” report for all databases, you can use the following query:

DECLARE @curr_tracefilename VARCHAR(500);
DECLARE @base_tracefilename VARCHAR(500);
DECLARE @indx INT;

SELECT @curr_tracefilename = path
FROM sys.traces
WHERE is_default = 1;

SET @curr_tracefilename = REVERSE(@curr_tracefilename);
SELECT @indx = PATINDEX('%\%', @curr_tracefilename);
SET @curr_tracefilename = REVERSE(@curr_tracefilename);
SET @base_tracefilename = LEFT(@curr_tracefilename, LEN(@curr_tracefilename) - @indx) + '\log.trc';

SELECT SUBSTRING(CONVERT(NVARCHAR(MAX), TEXTData), 36, PATINDEX('%executed%', TEXTData) - 36) AS command,
       LoginName,
       StartTime,
       CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(MAX), TEXTData), PATINDEX('%found%', TEXTData) + 6, PATINDEX('%errors %', TEXTData) - PATINDEX('%found%', TEXTData) - 6)) AS errors,
       CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(MAX), TEXTData), PATINDEX('%repaired%', TEXTData) + 9, PATINDEX('%errors.%', TEXTData) - PATINDEX('%repaired%', TEXTData) - 9)) repaired,
       SUBSTRING(CONVERT(NVARCHAR(MAX), TEXTData), PATINDEX('%time:%', TEXTData) + 6, PATINDEX('%hours%', TEXTData) - PATINDEX('%time:%', TEXTData) - 6) + ':' +
               SUBSTRING(CONVERT(NVARCHAR(MAX), TEXTData), PATINDEX('%hours%', TEXTData) + 6, PATINDEX('%minutes%', TEXTData) - PATINDEX('%hours%', TEXTData) - 6) + ':' +
               SUBSTRING(CONVERT(NVARCHAR(MAX), TEXTData), PATINDEX('%minutes%', TEXTData) + 8, PATINDEX('%seconds.%', TEXTData) - PATINDEX('%minutes%', TEXTData) - 8)) AS time
FROM ::fn_trace_gettable(@base_tracefilename, DEFAULT)
WHERE EventClass = 22
  AND SUBSTRING(TEXTData, 36, 12) = 'DBCC CHECKDB';

By running this query, you can obtain the same report for all databases without the need to manually generate it for each one.

As responsible DBAs, it is crucial to regularly run the CHECKDB command to ensure the consistency of our databases. How often do you run the CHECKDB command in your production environment? Let me know in the comments!