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.
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!
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!