Running DBCC CHECKDB on TEMPDB

First, a confession. I've been doing it wrong for a long time. Only recently have I learned that TempDB should be included in regular CHECKDB maintenance. Ok, now that I've got that off my chest, I can continue.

So now I've been diligently setting up this task on all servers. NB: TempDB is not available within Maintenance Plans, so you'll need to write the tsql in a job. I add it as a second step in my dbcc maintenance plan job.

The problem, however, is that database snapshots in TempDB are not available and SQL Server takes a TABLOCK on all the objects being checked. I kept getting the following error message when checkdb was run, causing my job to fail:

Object ID 1065627435 (object 'dbo.#TempTable_________________________________________________________________________________________0000000011BE'):  DBCC could not obtain a lock on this object because the lock request timeout period was exceeded.  This object has been skipped and will not be processed. [SQLSTATE 42000] (Error 5245). 

There were two options: 1. Schedule the maintenance task to run when nothing else was happening. Not really possible. 2. Write a script that checks for locking before it runs the DBCC command. I chose option 2.

I wrote a loop that runs until there are no exclusive locks within TempDB. I put in a delay so that the loop gives the database server a bit of a break in the event of long running queries. Here it is:


I hope this is helpful.