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.