Locking and DBCC CHECKDB

Question: We’re seeing our weekly consistency checking job fail with lock timeout errors when it gets to checking tempdb. Why is this? I thought DBCC CHECKDB runs online now without locks?

Answer: Yes, it is true that from SQL Server 2000 onwards, DBCC CHECKDB runs online by default. Even in SQL Server 2000 though, DBCC CHECKDB still took Schema-Stability locks on tables that were being checked as it could not cope with their metadata changing during a running consistency check. This was because SQL Server 2000 used transaction log analysis as its method of running online, and that didn’t work if the table metadata changed during the checks.

While this mechanism doesn’t take locks, DBCC CHECKDB does very aggressive data file page readahead which can flood the I/O subsystem, causing a large drop in workload throughput that seems consistent with blocking problems. What you’re seeing though is real blocking.

There are two cases when DBCC CHECKDB is forced to take locks as part of its operations: if you specify the TABLOCK option, or if the database snapshot cannot be created, and DBCC CHECKDB runs with the TABLOCK option implied. The latter occurs when running consistency checks on the tempdb database.

The TABLOCK option means that an Exclusive database lock will be held to run the allocation checks (and this is skipped for tempdb, as an Exclusive database lock for tempdb cannot ever be acquired), and then Share locks are acquired on the database tables as they are checked (and this does happen for tempdb).

If a Share lock cannot be acquired, as message like the one below will be output:

If you see one of these, figure out which database is being consistency checked.

If the database is tempdb, then some other process is holding a lock on the table concerned and the blocking is just natural. Note that DBCC CHECKDB does not use the instance-wide lock timeout (which is usually set to infinite) – it uses a timeout of 20 seconds.

If the database is not tempdb, someone has specifically asked for the TABLOCK option to be used. The only reason to use the TABLOCK option is to prevent the database snapshot from being created – either for performance reasons or because of lack of disk space for the database snapshot. It’s not common to see this option being used.