Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot?

Day 1 of 31 Days of Disaster Recovery: Does DBCC Automatically Use Existing Snapshot?

Welcome to my series on Disaster Recovery. I will spend the entire month of January focusing on all things related to disaster recovery including topics like corruption, data integrity, data loss, DBCC commands, and more.

For my first post of this month, I want to take a look at the myth that the DBCC CHECK commands will automatically use an existing database snapshot if one exists for the database. This is a myth I believed myself at one time and told to others. This was done to prove it to myself as much as to anyone else. This is an attempt to prove definitively that the DBCC CHECK commands will not use an existing snapshot for a database.

Does DBCC Automatically Use Existing Snapshot?

I was doing some bigger investigations into DBCC CHECK commands (post to come) and was looking for a way to see that hidden snapshot the DBCC CHECK commands create and use. The snapshots are not visible in sys.databases, sys.master_files, nor any other system catalog that I could find. Additionally, the snapshot creation does not trigger server level events for a DDL trigger nor the database create or database start events for SQL Trace or Extended Events.

I found it in the [i]databases_dbcc_logical_scan[/i] event via Extended Events. The event reports database_id of the database the command was run against as well as the database_id of the database where the action is actually occurring. If the action database is the hidden snapshot, the database_id will not show up in sys.databases, but the DB_NAME() function will return the name of the source database. I set up an Extended Events session to capture this event with the columns database_id and database_name. I use the ring buffer target because I don’t intend to retain any of this data. This session will not be active yet.

CREATE EVENT SESSION [TestSnap] ON SERVER 
ADD EVENT sqlserver.databases_dbcc_logical_scan(
    ACTION(sqlserver.database_id,
        sqlserver.database_name)) 
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB);

Then I create a snapshot of the AdventureWorksDW2012 database called AWSnap so there is an existing snapshot.

CREATE DATABASE AWSnap
    ON (NAME = N'AdventureWorksDW2012_Data',
        FILENAME = N'C:\bak\AdventureWorksDW2012_Data.ndf')
        AS SNAPSHOT OF AdventureWorksDW2012;

Now, using Object Explore in SQL Server Management Studio (SSMS), I start the Extended Events session (expand Management -> expand Extended Events -> right-click on the session -> click Start Session). Then I right-click on the session and click on Watch Live Data. This allows me to see the events in real-time while DBCC CHECKDB is running against the AdventureWorksDW2012 database. Next I open a new query window and run DBCC CHECKDB.

We can see by looking at any one entry in the Extended Events session that the database_id and database_id (Action) are different database IDs. If you query sys.databases, you will see that database_id of 6 is the AdventureWorksDW2012 database and there is no entry for the database_id (Action). If we check the action database_id with the DB_NAME() function while the CHECKDB is running, it will return AdventureWorksDW2012, but after DBCC completes, it will return NULL.

For the next part of the test, I close out the Live Data window and reopen it so that it is all clear again. Then I run DBCC CHECKDB against the snapshot AWSnap. checking the Live Data window, we see that the database_id and database_id (Action) are now the same value. If you query sys.databases, you will see that the value for both maps to the snapshot AWSnap.

Summary

As I have shown here, if you want to control the snapshot that DBCC uses, you need to specifically run DBCC on the snapshot itself. It won’t detect and use the snapshot just because one exists. The reason why this is important to understand will become apparent in a later post. So be sure to check back and see how I build on this information.