SQL Server misunderstanding about 2nd-day DBCC CHECKDB may cause blocking

Misunderstanding #2: dbcc checkdb will cause blocking, because this command will lock by default

This is wrong!

In SQL Server 7.0 and earlier versions, the essence of dbcc checkdb commands is that the C language implements a continuously nested loop code and adds a table lock to the table (the time complexity of the nested loop algorithm is the Npower of the number of nesting times. You can understand it as a programmer ), this method is not harmonious and .....

In the era of SQL Server 2000, a buddy named Steve Lindell (still in the SQL Server Team) used the method of analyzing transaction logs to check Database Consistency and overwritten the DBCC CHECKDB command. Dbcc checkdb prevents log truncation. When the log is read from the beginning to the end, some Recovery operations are performed inside the transaction log. This is actually another completely new code for implementing Recovery, but only within the CHECKDB command. However, this method still has problems. For example, this command may fail to be checked. If the check fails, you need to re-execute it to see if the same error still exists. And sometimes, this command will also use the SCH_S lock, although this lock only blocks changes to the table scan and table architecture, but the code for checking consistency through logs is not perfect, and .....

In the SQL Server 2005 era, a guy named Paul Randal (TRANSLATOR: author of this article) re-wrote the DBCC CHECKDB command. This time, database snapshots are used to check consistency (because database snapshots provide consistency views at a specific time point in the database). Therefore, no transaction log analysis code is available, no locks are available. Because no locks need to be applied to the original database to access database snapshots, the buffer pool automatically handles possible resource contention.

For more information, read the following article:

• CHECKDB From Every Angle: Complete description of all CHECKDB stages

• CHECKDB From Every Angle: Why wowould CHECKDB run out of space?

• Database snapshots-when things go wrong

• Issues around dbcc checkdb and the use of hidden database snapshots

• Do transactions rollback when dbcc checkdb runs?

• Diskeeper 10 Intelliwrite failed uption bug

Now, in any SQL Server version, if you still use the WITH TABLOCK prompt, a table lock will be generated to ensure transaction consistency. But I do not recommend this method. This method will not only take a longer time, but will also try to apply an exclusive lock to the database. However, the connection that has been active in the database may cause this method to fail.

In SQL Server 2000, blocking transaction log truncation will cause problems related to abnormal log growth, but for SQL Server 2005, this command will cause snapshot-related problems (For details, refer to the above link ).

However, by default, dbcc checkdb no longer blocks since SQL SERVER 2000.