DBCC CHECKDB on Secondary Replica

Question

I have an AlwaysOn AG. The secondary replica is set as readable. But, when i run DBCC CHECKDB on secondary replica, it is throwing below error:

DBCC CHECK cannot proceed on database because it is a secondary replica and snapshot creation failed. Secondary replica databases cannot be exclusively locked for DBCC CHECK.

Can you please explain why CHECKDB is failing on secondary.

Answer 1

The error you are encountering when running DBCC CHECKDB on a secondary replica in an Always On Availability Group is due to the fact that secondary databases cannot be exclusively locked for the DBCC CHECKDB operation. When a database is set as a secondary replica, it is primarily meant for read-only access, and certain operations, such as creating a snapshot for consistency checks, are restricted.

In this case, the DBCC CHECKDB command fails because it requires an exclusive lock to perform its checks, which is not possible on a secondary replica where the database is still receiving updates from the primary replica. Therefore, the snapshot creation fails, leading to the error message you see.

To work around this limitation, you might consider running DBCC CHECKDB on the primary replica instead, or using the WITH TABLOCK option, which can help avoid the need for a snapshot by locking the entire table during the check, but this may not be applicable on a secondary replica due to its read-only nature.

For more information on this topic, you can refer to the limitations and restrictions associated with readable secondary replicas in Always On availability groups.

Answer 2

You can indeed run DBCC CHECKDB on a secondary, no matter if it is readable or not. I just tested.

But as you may know, DBCC CHECKDB works with a hidden snapshot of the database, and the error message says that this snapshot creation failed. I guess there can be more than one reason for this, but one common problem is fragmentation in the file system. You could check the SQL Server errorlog if there are any supplementary messages.

Beware that when you are running CHECKDB on a secondary replica, you are checking the integrity of the secondary. This is certainly worthwhile doing, in case you need to fail over to the secondary at some point. But it does not prove that the primary is OK. That is the primary can be corrupt, even DBCC CHECKDB comes out clean on the secondary - and vice versa. The primary may be OK, even if the secondary is corrupt. This is because what is transported between the nodes are log records, but corruption occurs when something goes wrong when writing to the data file.