I had a fantastic time at the SQL Server 2008 JumpStart last week in Redmond. Getting to spend a whole week learning new stuff from Kim and Paul was awesome! My favorite topic was one from Paul called "DBCC CHECKDB Fundamentals in only 487 slides." Although I am still absorbing much of that information, it did remind me of a DBCC CHECKDB trick that I have found very useful and have been meaning to post.

SQL Server 2005 makes your DBCC CHECK* commands much more user-friendly by running them against a hidden database snapshot. If you haven't read about snapshots, you should do that at your earliest opportunity, and I'm not going to go into a full discussion of their benefits and uses here. The short explanation is that a database snapshot is a read-only copy of a database, that is extremely fast to create. It reserves the total amount of space needed for the database, but doesn't actually copy the data.

Running your DBCC commands against a snapshot means that you will not suffer concurrency problems if users are trying to modify the tables that DBCC is trying to validate, which can be a good thing. DBCC won't block your users, and your users won't block the DBCC commands. Keep in mind that this snapshot based DBCC is not available if you are using the REPAIR mode, because a database snapshot is read-only.

One big drawback of the DBCC snapshot is that SQL Server has to reserve the maximum amount of space that the snapshot could ever use, and because the snapshot for DBCC is hidden, you have no control over where the space gets reserved. SQL Server will try to reserve the space on the same volume where the database exists. If you don't have enough space available for a full copy of the database, you might not be able to run DBCC.

One alternative is to run DBCC a table at a time, and then SQL Server only needs to reserve enough space for each table. However there are many checks that DBCC CHECKDB will do in addition to checking each table, so you will not be able to perform all these checks. 

Another option is to use the TABLOCK keyword, which forces SQL Server to NOT create a snapshot and to take a table lock as each table is being checked, but then basically you are using the old flavor of DBCC and not getting any benefits of the SQL Server 2005 version.

So I think the best solution, if you don't have the disk space on the same drive as your database, is to create a NON-hidden snapshot, i.e. just a regular user-defined database snapshot. You can create your own snapshot wherever you like (wherever you have disk space) and then run your DBCC CHECK* commands against your own snapshot.

So you really need to go read about creating database snapshots now!

In addition to BOL, there is more info in my Storage Engine book,  and in an article I wrote for SQL Server Magazine.

Have fun!

~Kalen