Capturing DBCC CHECKDB Output

If you haven’t experienced database corruption in your SQL Server career and had to pore over DBCC CHECKDB output, you’re a lucky soul.  If you encounter corruption, the most important thing you can have is the output, because this helps determine your next course of action.

Step 1

First, you need to be running CHECKDB on a regular basis.  If you’re going to ask what you mean by regular basis, I’ll direct you to a post of Paul’s that is still relevant: Importance of running regular consistency checks.  There are a few factors to consider, but in general I recommend at least once a week.  If you’re a 9 to 5 shop and have the maintenance window to run CHECKDB daily, feel free to do that.

Step 2

Second, running CHECKDB and capturing the output should be an automated process.  SQL Agent is your friend here, and you have a few options.

Option 1: Set up an Agent Job

Within SSMS, you can create an Agent job to run DBCC CHECKDB and write the output to a file.  I recommend the following syntax, which is good for most databases:

DBCC CHECKDB ('YourDBNameHere') WITH NO_INFOMSGS;
GO

Note: If you have a very large database (1TB or larger), or don’t have the maintenance window to run a complete CHECKDB, then you may want to look at another one of Paul’s posts for options, Consistency Checking Options for a VLDB.

Within the Agent job, it’s pretty straight-forward:

Running CHECKDB in an Agent job

Running CHECKDB in an Agent job

 

To make sure the CHECKDB output is saved, you want to write it out to a file, which you configure on the Advanced tab:

CHECKDB output file configuration

CHECKDB output file configuration

 

The configuration of the output file is important.  If you enter a path and file name like this:

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Log\checkdb_output.txt

then every time the job runs it overwrite the contents of this file.  You have an option to append the file, but then you have to make sure you manage the size of the file.  Instead, append the date and time to the file to make it unique:

C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Log\checkdb_output_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt

You also need to create a job to clean up output files regularly.  With this configuration, you can view the CHECKDB output at any time, as long as the file still exists in the folder specified.

Option 2: Use Ola’s Agent Job

The syntax above works for one database…but what if you have 10 user databases on your instance?  And what about system databases?  Well, you can add the commands for each database, or you can make life easy and use Ola Hallengren’s scripts for maintenance, which creates two jobs for integrity checks: one for user database, and one for system databases.

SQL Agent jobs created by Ola's maintenance script

SQL Agent jobs created by Ola’s maintenance script

 

Ola’s stored procedure, dbo. DatabaseIntegrityCheck, has multiple options for configuration.  By default it checks all tables and indexes, and outputs error messages, and the job writes an output file each time it executes (including date and time in the name) to the instance Log folder by default.  When multiple databases are checked, the output for all databases is included in one file.  It’s also worth noting that Ola’s scripts create an Output File Cleanup job to clean up those files.  By default NONE of these jobs are scheduled, so I recommend you review what the job does, confirm it is configured how you want, and then create a schedule.

Option 3: Use a Maintenance Plan

In every environment I manage directly, I use Ola’s scripts for maintenance tasks.  However, some folks may be more comfortable using Maintenance Plans.  If that’s true for you, then when you set up the plan for integrity checks, there are two things I recommend.  First, you typically do not want to run with the PHYSICAL_ONLY option unless the check takes an extremely long time to run and you’re trying to make the task fit into a maintenance window.  When you use the PHYSICAL_ONLY option the logical checks, inter-page checks, and things like DBCC CHECKCATALOG will be skipped.

Options in the CHECKDB maintenance task

Options in the CHECKDB maintenance task

 

Second, the maintenance task writes to an output file by default.  Within the UI, you can select the Reporting and Logging button to confirm the location.

Reporting and logging for CHECKDB maintenance plan

Reporting and logging for CHECKDB maintenance plan

 

By default, it writes to the Log folder for the SQL Server installation (e.g. C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Log for SQL Server 2019).  Each file is created with the date and time as part of the filename.  As with other options, you need to ensure that these output files are cleaned up on a regular basis.

Other Items to Consider

If you run DBCC CHECKDB in SSMS via a machine you have RDP’d to, be aware that depending on the group policy your session can disconnect after a certain amount of time.  I’ve had this happen in client environments, and if you’re trying to run CHECKDB, it will stop and you won’t get any output…hence the suggestion to use an Agent job.

With any Agent job you use for integrity checks, I also recommend (dare I say insist?) that you enable notifications for job failure.  This is configured on the Notifcations tab of the job, and requires an Operator and database mail.  Create an Operator that writes to a team alias (e.g. DBAs@yourcompany.com) rather than just you.

Agent Job Notifications

Agent Job Notifications

 

Summary

If you are responsible for any SQL Server instance, you want to make sure of the following:

  1. You are running DBCC CHECKDB on a regular basis to look for integrity issues.
    1. This process should be automated.
  2. Your method for checking should include an option to write the CHECKDB output to a file that can be referenced later.
    1. If you find corruption, make a couple copies of this file.  If it gets deleted, you will have to run CHECKDB again to determine what is corrupt.
  3. Set up Notifications for the Agent job so you are notified if corruption is found.

I hope that the information in this post was review for you, but if it wasn’t, or if you have encountered corruption, feel free to reach out to see if we can help.

 

SQLskills 101: The Other Bad Thing About Clearing Procedure Cache

Every time you execute a query against SQL Server, it goes through optimization and compilation and a query plan is generated. Most of the time that plan goes into cache (there’s always exceptions, which is a discussion for a separate time), which means that the next time that query executes, SQL Server can use the same plan.  Now, re-using the same plan is typically a good thing; SQL Server doesn’t have to go through full optimization and compilation again.  But sometimes that plan isn’t ideal for the query and you want to remove it from the plan cache.  You can do this manually.  Plans can also age out if they’re not being used, but if I want to purposely evict a plan from cache, I have several options:

Ideally, you should remove only what’s absolutely necessary.  Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated.  Glenn gives examples on how to use each statement (and others) in his post Eight Different Ways to Clear the SQL Server Plan Cache, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.

For this demo script, I recommend running it against a TEST/DEV/QA environment because I am removing plans from cache which can adversely affect performance.

We’ll  run one statement and one stored procedure multiple times against the WideWorldImporters database:

/*
Create a stored procedure to use for testing
*/
USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Application].[usp_GetCountryInfo];
GO

CREATE PROCEDURE [Application].[usp_GetCountryInfo]
@Country_Name NVARCHAR(60)
AS
SELECT *
FROM [Application].[Countries] 
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = .[CountryID]
WHERE .[CountryName] = @Country_Name;
GO

/*
Remove everything from cache
*/
DBCC FREEPROCCACHE;
GO

/*
Run the stored procedure 20 times
*/
EXECUTE [Application].[usp_GetCountryInfo] N'United States';
GO 20

/*
Run the query 20 times
*/
SELECT
[s].[StateProvinceName],
[s].[SalesTerritory],
[s].[LatestRecordedPopulation],
[s].[StateProvinceCode]
FROM [Application].[Countries] 
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = .[CountryID]
WHERE .[CountryName] = 'United States';
GO 20

Now let’s see what’s in cache, and what’s also in sys.dm_exec_query_stats (which gives us execution statistics).

SELECT
[qs].[last_execution_time],
[qs].[execution_count],
[qs].[total_logical_reads]/[qs].[execution_count] [AvgLogicalReads],
[qs].[max_logical_reads],
[t].,
[p].[query_plan]
FROM sys.dm_exec_query_stats [qs]
CROSS APPLY sys.dm_exec_sql_text([qs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([qs].[plan_handle]) [p]
WHERE [t]. LIKE '%Application%';
GO
Text, plan, and query stats from the DMOs

Text, plan, and query stats from the DMOs

 

 

 

 

Cool, I see that each one executed 20 times, and in addition to the text and the plan I pulled information about I/Os.

Now let’s clear procedure cache, and we’ll take the good old sledgehammer approach because we’re lazy and run DBCC FREEPROCACHE.  Then run the query to see what’s in sys.dm_exec_query_stats…

DMO information *after* running DBCC FREEPROCCACHE

DMO information *after* running DBCC FREEPROCCACHE

 

 

 

 

 

 

Do you see what happened?  In addition to removing the plan, we also lost all the query execution stats from dm_exec_query_stats.  We have no history about query performance (unless we were writing it to another table on a regular basis, or have a third-party tool that captures baselines).  If you don’t believe me, just run SELECT * FROM sys.dm_exec_query_stats…all that data is gone (but will start to re-populate as you run queries and plans go into cache).

This is important to know! If you’re troubleshooting a performance issue and you want to try removing the plan from cache, be aware that you’re removing execution statistics too (and maybe save those off first in case you need them for reference/comparison later).

DBCC CLONEDATABASE Cannot insert duplicate key Error

If you’ve been using DBCC CLONEDATABASE at all, you might have run into a cannot insert duplicate key error (or something similar) when trying to clone a database:

Database cloning for ‘YourDatabase’ has started with target as ‘COPY_YourDatabase’.
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘sys.sysschobjs’ with unique index ‘clst’. The duplicate key value is (1977058079).

If you do some searching, you’ll probably end up at this Connect item: DBCC DATABASECLONE fails on sys.sysowners.

The Connect item states that the problem exists because of user objects in model.  That’s not the case here.

I’m working with a database created in SQL Server 2000…now running on SQL Server 2016.  It turns out that when you create new user tables in a SQL Server 2000 database, the first objects have the IDs 1977058079, 2009058193, and 2041058307.  (Yes, we actually tested this…I had a copy of SQL 2000 in a VM but Jonathan installed one and dug into it to figure out the IDs.)  There were a lot of changes between SQL Server 2000 and SQL Server 2005, and that included changes to model.  In the model database in SQL Server 2005 and higher, the aforementioned IDs are used for QueryNotificationErrorsQueue, EventNotificationErrorsQueue and ServiceBrokerQueue respectively…Service Broker objects.  As a result, my user database – created in SQL Server 2000 – has user objects with IDs that are the same as system objects in model (and of course when the system objects like QueryNotificationErrorsQueue, EventNotificationErrorsQueue and ServiceBrokerQueue got created in my user database, they got completely different IDs).

Edit 2017-06-15: This issue is fixed in CU3 for SQL Server 2016 SP1 and CU5 for SQL Server 2014 SP2! See KB 4016238, FIX: DBCC CLONEDATABASE is unsuccesful if the source database has an object originally created in SQL Server 2000.

To determine if this is an issue for any of your user databases you can use the following queries (change user_database to the name of your DB):

/*
lists tables in the user database that have an ID that matches an object in model
*/
SELECT m.id, m.name, c.name, c.id, m.type
FROM model.sys.sysobjects m
FULL OUTER JOIN user_database.sys.sysobjects c
ON m.id = c.id
JOIN user_database.sys.objects o
ON c.id = o.object_id
WHERE o.is_ms_shipped != 1
AND m.id IS NOT NULL;

/*
lists system objects in the user database that do *not* match the id of the same object in model
(FYI only)
*/
SELECT m.id, m.name, c.name, c.id, m.type
FROM model.sys.sysobjects m
FULL OUTER JOIN user_database.sys.sysobjects c
ON m.name = c.name
JOIN user_database.sys.objects o
ON c.id = o.object_id
WHERE m.id != c.id

To workaround this, you need different IDs for the affected objects in the user database.  Now, I could recreate my tables in the user database, confirm the ID isn’t used for any system object in model, and then move the data over…  But I have some tables that are larger than 100GB, so it’s not an easy fix.

If you’re interested in reproducing the issue, sample code is below (thanks Jonathan).  You need SQL Server 2000 installed and at least SQL Server 2005 or 2008 or 2008R2.   You cannot restore the SQL 2000 backup to SQL Server 2016 directly, you must do an intermediate upgrade to 2005, 2008, or 2008R2, then go to 2016.  Paul has tested upgrading a SQL Server 2005 database to a newer instance here; but for SQL Server 2000 you have to perform the intermediate upgrade.

Code to create the issue

DROP DATABASE New2000
GO

CREATE DATABASE New2000
GO

USE New2000
GO

DECLARE @loop INT
SET @loop = 0

WHILE @loop  < 3
BEGIN

	DECLARE @table NVARCHAR(4000)

	SET @table = 'table_' + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 121), ':', ''), '-', ''),  ' ', ''), '.', '')

	DECLARE @sql NVARCHAR(4000)

	SET @sql = 'CREATE TABLE ' + @table+ ' (RowID INT)';

	EXEC(@sql)

	SET @Loop = (select COUNT(*) FROM sysobjects WHERE id IN (
		1977058079,
		2009058193,
		2041058307)
		)

	IF @loop = 0
	BEGIN
		PRINT 'Dropping Table'
		SET @sql = 'DROP TABLE '+ @table
		EXEC(@sql)
	END

	WAITFOR DELAY '00:00:00.010'

END

/*
	Backup the database in SQL Server 2000
	Restore in SQL Server 2016
*/

DBCC CLONEDATABASE ('New2000', 'COPY_New2000');
GO