Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a 2Tb+ SAP database with 8 datafiles, the first files have grown to fill the available disk space on our HP EVA5000, and the growth is now on the new files. We upgraded SQL from SQL server 2000 to 2005 and Checkdb works differently, and now fails with no space to create snapshot. We have added 50Gb to each file as a temporrary measure while we precure a new I/O subsystem. The Question is: How can we monitor snapshot usage? What will happen if Checkdb starts now it has more space, then fills up? and can you cancel CheckDb if you see that its going to run out of space?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Ooh I just read this last night in SQL Server 2008 Internals book (thanks Paul Randal). If it runs out of space the snapshot gets deleted. What I mean is in SQL 2005+ the internal engine is actually creating a snapshot behind the scenes and running checkdb against that. You can't access this snapshot as its invisible to you but it still takes up space on the filesystem the files are on. If it doesn't have enough space to create this system snapshot it will throw an error and rollback that snapshot.

What you can do is manually create a snapshot of the database on another volume that has enough space and run checkdb against that. Your results should still be valid as the snapshot is simply a collection of differences in page changes against the base data. I'm sure Paul could answer this more eloquently but this is the basic gist of it.

Also yes, you can cancel a running checkdb query.

share|improve this answer

Take a look at this article on Paul's blog which answers your questions:

http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Why-would-CHECKDB-run-out-of-space.aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.