Generating A Database Snapshot For DBCC CHECKDB

Many people don’t realize that DBCC CHECKDB actually uses an internal database snapshot of the database to run the consistency checks on. (See the “Internal Database Snapshot” section of the DBCC CHECKDB MSDN Docs for more information.) This snapshot is created along side your “live” database, and uses the same drive(s) that your database uses. While taking this snapshot can be disabled with the use of TABLOCK, it is usually recommended to allow the snapshot because of the locking SQL Server has to do on the database to run DBCC CHECKDB.

But, there may be times when you want to control where the snapshot DBCC CHECKDB uses is placed, and have it in a different location than your live database. For example, you may not have enough hard drive space for a snapshot to sit beside your production database, or maybe your database is on a SSD and you want the snapshot to be placed on a HDD.

As far as I know, there is no option for CHECKDB to specify where it should place the snapshot. In cases like this, you have to generate your own snapshot and run CHECKDB on that snapshot instead of your live database. This is actually pretty easy to do:

It is important to note the use of the TABLOCK option in the DBCC CHECKDB command above. TABLOCK causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This means that if you run DBCC CHECKDB on your MyDB_SS snapshot without using TABLOCK, you will actually be taking another snapshot of your snapshot (*see edit below). Normally, when running DBCC CHECKDB you would probably want it to take that snapshot, as it prevents blocking and concurrency problems in your live database. However in this case, since we already manually created our snapshot, TABLOCK is necessary to ensure that SQL Server doesn’t create another snapshot. (*see edit below)

*Edit – After publishing this post, Paul Randal told me that CHECKDB actually does not create a snapshot if the database it’s pointed at is already a snapshot.  This is good to know, and means that TABLOCK actually IS NOT required when running DBCC CHECKDB on a user generated snapshot, as I originally said that it was.