An Idea For Improving DBCC CHECKDB

Hard Enough

At this point, we’ve all probably got a database that’s a terabyte or more in size. If you’re anything like I was, you’ve got a ton of multi-terabyte databases, and the SAN admin has a special folder for your emails.

When you’ve got a lot of large databases, normal maintenance is out the window, and free disk space can be tough find.

Fond Memories

There are a lot of reasons that CHECKDB can fail that don’t indicate corruption. That doesn’t make the email alert any less scary. Some examples are:

  • 1823: “A database snapshot cannot be created because it failed to start.”
  • 3313: “During redoing of a logged operation in database ‘%.*ls’, an error occurred at log record ID %S_LSN. Typically, the specific failure is previously logged as an error in the operating system error log. Restore the database from a full backup, or repair the database.”
  • 5128: “Write to sparse file ‘%ls’ failed due to lack of disk space.”
  • 7928: “The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.”
  • 8921: “Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.”

These errors all show up together when the drive that your databases reside on doesn’t have enough room for the snapshot that DBCC CHECKDB takes and uses to be created.

Right now, we don’t have any control over that.

That’s what I wanna change: Option to specify a location for the DBCC CHECKDB snapshot

Bonus: Ola would support it!

Doodily doo

Corruption is a tough cookie. Checking for it with large databases ain’t one bit of fun. Breaking the checks down into smaller parts, or offloading are okay options, but add a layer of complexity that not every team wants to manage.

At least until SQL Server cleans its own room.

Thanks for reading!

Previous Post
Fast Food Code
Next Post
Why Do Some Indexes Create Faster Than Others?

7 Comments. Leave new

  • This is a small niggle, but you can roll this yourself. Specifically, you can create the snapshot yourself wherever you’d like and run checkdb against that snapshot. While I agree that what you’re suggesting would be nice, it’d be syntactic sugar.

  • alen teplitsky
    January 7, 2019 11:10 am

    If your datacenter has a junk pile of old servers and hard drives you can put together a cheapo server, restore the 1TB database to it and run checkdb there.

  • Just to clarify – Is there any reason why I would need to or should run CHECKDB on production if I have the capacity and licencing to run it on a restored version on another server?

    • My two cents – run it on your other server if there aren’t any licensing implications. Given that it’s restored, presumably there will be no i/o on it and so the space needed for the snapshot will be negligible (since snapshots are a “copy on write” technology). Also, you’re not competing for resources with your production workload.

  • Minion CheckDB does custom snapshots.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.