CHECKDB and TempDB May 24th, 2010

Vinod Kumar

Continuing the conversations around CHECKDB and my session at TechEd. Here is another interesting trivia about CHECKDB and its usage against TempDB. If you remember the post around CHECKDB going ONLINE on the blog the usage of Snapshot internally is something unique. If you get into the documentation of DATABASE SNAPSHOTs on BOL you can read – we cannot create a SNAPSHOT on system databases including TEMPDB. The only exception is MSDB but that is a conversation for another day :) …

So our internal snapshot for CHECKDB also fails gracefully and you will notice this when you issue the command against TEMPDB.

DBCC results for ‘tempdb’.
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.

This is because of the behavior of Tempdb with ability to recover the database on restart. You cannot create a database snapshot on the Tempdb database.

Interesting hidden within the message – the CHECKCATALOG and Service Broker checks are skipped. On closer inspection – it is not possible to run CHECKALLOC on Tempdb too.

The check statement was aborted. DBCC CHECKALLOC cannot be run on TEMPDB.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There are undocumented trace flags that can force this action but those are surely out-of-scope and most importantly, unsupported :). BTW, while I was on the first error – even the CATALOG are not checked and you can confirm it from CHECKCATALOG command which also fails just like above.

The check statement was aborted. DBCC CHECKCATALOG cannot be run on TEMPDB.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When it comes to TempDB corruptions, a restart will solve the same. But I highly recommend further investigation to the root cause analysis because you dont want to be in this situation over a weekend when you are on vacation. Mostly, errors comes from IO subsystem and that should be your first point of troubleshooting. Then move to the next layers.

Share this article

Tags: , , , , , ,

This entry was posted on Monday, May 24th, 2010 at 16:45 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply