THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: DBCC's Hidden Snapshot

 

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

Published Sunday, February 10, 2008 3:31 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hugo Kornelis said:

Hi Kalen,

Great idea, thanks!

Unfortunately, only available to users with the Enterprise Edition, since other editions don't allow creation of database snapshots :(

Best, Hugo

February 11, 2008 3:19 AM
 

Kalen Delaney said:

Ah, yes. Thanks so much Hugo, for pointing out that user-defined snapshots can only be created with Enterprise (or Developer) edition. The hidden snapshot is created no matter what edition you're using; after all, SQL Server itself can get around its own restrictions. ;-)

So if you are not using Enterprise, and you don't have the disk space for the snapshot, you'll have to try one of the other suggestions I gave.

February 11, 2008 12:25 PM
 

James Luetkehoelter said:

That is a great idea, I remember the article in SQL Mag. I referred a *lot* of people to it. The thing I love about database snapshots is that we've only scratched the service of where they can be useful. They are far more than a user-level disaster recovery tool...

February 12, 2008 12:35 PM
 

Paul Randal said:

Slight correction - a database snapshot does not reserve any space - it grows only as pre-modify images of pages are pushed into it from the source database. It's a common misconception that a database snapshot needs the same size as the database its snapshoting. The problem when running CHECKDB comes if you don't have much free space on the volume hosting the source database files, and lots of update activity happens while CHECKDB is running - causing it's snapshot to grow excessively. See http://www.sqlskills.com/blogs/paul/2007/09/15/CHECKDBFromEveryAngleCompleteDescriptionOfAllCHECKDBStages.aspx for more in-depth info.

Thanks

February 14, 2008 6:17 PM
 

Kartar Rana said:

PARA 3, LINE 3

If you don't have enough space available for a full copy of the database, you might not be able to run DBCC

It acquires table level locks on the database but does not fail if you are running out of space.

February 13, 2014 9:19 AM
 

Kalen Delaney said:

Kartar

As mentioned, there are pre-existing conditions that don't allow the snapshot to be created, and in those cases, the DBCC command will run in locking mode instead of in a snapshot. But if you start running with the snapshot, and then run out of space, you will get an error. As Paul corrected me, the space is not reserved up front, but you need to be aware that you could need up to the size of your database in free space.

There is no way for SQL Server to check ahead of time if you have enough space for your snapshot, because the space requirements depend on how much concurrent work you'll be doing in the database.

~Kalen

February 13, 2014 2:50 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement