SQL Server misunderstanding 30th on the 2nd day DBCC checkdb can cause obstruction _mssql

Myth #2: DBCC checkdb can cause blocking because this command is locked by default

This is wrong!

In SQL Server 7.0 and earlier versions, the nature of the DBCC checkdb command is a constantly nested loop of code implemented in C and table locking (cyclic nesting algorithm time complexity is the number of nesting n times, as programmers you know), this way is not harmonious, and ...

In the SQL Server 2000 era, a buddy named Steve Lindell (now still in SQL Server team) has overridden the DBCC CHECKDB command by using the method of analyzing the transaction log to check the consistency of the database. DBCC checkdb blocks logs from being truncated. When the log is read from the beginning to the end, some sort of recovery operation is done inside the transaction log, which is actually another new implementation of recovery code, but only within the CHECKDB command. But there are still problems with this approach, such as the possibility of a check failure for this command, and if the check fails, you will need to rerun it to see if the same error occurs. And sometimes, this command will also use sch_s lock, this lock is only blocking the table scan and table schema changes, but the log to check the consistency of the code is not perfect, and ...

In the SQL Server 2005 era, a guy named Paul Randal (translator: The author of this article) rewrote the DBCC CHECKDB command again. This time using a database snapshot to check for consistency (because the database flash provides a consistent view at a specific point in the database), so there is no more parsing code for the transaction log, no more locks--because accessing a database snapshot does not require any locks on the original database, the buffer pool automatically handles possible resource contention.

To learn more about insider information, you can read the following article:

• CHECKDB from Every angle:complete description of all CHECKDB stages

• CHECKDB from Every Angle:why would CHECKDB run out of spaces?

• Database snapshots-when things go wrong

• Issues around DBCC CHECKDB and the use of hidden database snapshots

• Does transactions rollback when DBCC CHECKDB runs?

• Diskeeper intelliwrite corruption bug

Now, in any version of SQL Server, if you still use the WITH TABLOCK hint, that will create a table lock to ensure transactional consistency. But I don't recommend it that way. Because this approach not only takes longer, it will try to add exclusive locks to the database, but a connection that has already been active in the database may cause this to fail.

In SQL Server 2000, this command prevents transaction log truncation from causing problems related to log abnormal growth, but for SQL Server 2005来, this command causes snapshot-related problems (see the link above).

However, by default, DBCC CHECKDB will no longer be blocked since SQL SERVER 2000.