DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS fails when run on Secondary replica

Question

Create the simplest database possible:

CREATE DATABASE DatabaseCheck

Add this database to an Availability Group, where secondary is not readable

On primary run:

DBCC CHECKDB ('DatabaseCheck') WITH EXTENDED_LOGICAL_CHECKS

It succeeds:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DatabaseCheck'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Run the same DBCC command on secondary. It fails even though no errors were found:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DatabaseCheck'. Msg 0, Level 11, State 0, Line 1 A severe error occurred on the current command. The results, if any, should be discarded.

• Removing WITH EXTENDED_LOGICAL_CHECKS makes the error go away
• Making the secondary replica readable makes the error go away
• Doing a failover and checking the new primary makes the error go away. Checking the new secondary shows the error again.

Any idea what is this about? Checking DBCC CHECKDB documentation doesn't say anything about EXTENDED_LOGICAL_CHECKS being unsupported on non-readable secondary replica. Looks like a bug in the command itself to me.

One thing I noticed in the command output is that it checks statistics like here:

Verified integrity of statistics 'sys.sysrscols.clst'. Verified integrity of statistics 'sys.sysrowsets.clust'. Verified integrity of statistics 'sys.sysrowsets._WA_Sys_00000002_00000005'. Verified integrity of statistics 'sys.sysrowsets._WA_Sys_00000003_00000005'. Verified integrity of statistics 'sys.sysrowsets._WA_Sys_00000006_00000005'. Verified integrity of statistics 'sys.sysrowsets._WA_Sys_00000004_00000005'. Verified integrity of statistics 'sys.sysrowsets._WA_Sys_00000005_00000005'. Verified integrity of statistics 'sys.sysrowsets._WA_Sys_00000008_00000005'.

These are absent when run on secondary

Reproduced on:

Microsoft SQL Server 2019 (RTM-CU23) (KB5030333) - 15.0.4335.1 (X64) Sep 21 2023 17:28:44 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )

Edit:

The only message in error log is this:

DBCC CHECKDB (DatabaseCheck) executed by Domain\user found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000034:000001b1:0001 and first LSN = 00000034:000001af:0002.

There are no recent dumps in sys.dm_server_memory_dumps

Answer 1

I´ve had an Ticket filed to Microsoft, because we were running into this "issue" in our production environment. I hope this clarifies it for anyone, because the documentation were not that obvious about it in the first place.

Short answer: Extended Logical Checks require read access to the db.

Long answer from the ticket:

Issue:

The customer is trying to run “DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS” on a secondary non-readable replica and it is failing with: Error: 976 State 14. Please see fig.1. This is reproducible on SQL 2019 & 2022 (latest CU). If secondary replica is marked as readable then this process is successful without any errors.

 

Resolution:

I can confirm that this behaviour is by design. As per our documentation when using “EXTENDED_LOGICAL_CHECKS” (…logical checks are performed on an indexed view, XML indexes, and spatial indexes…).

  DBCC CHECKDB (Transact-SQL) - SQL Server

 

 

This behavior is by design because to perform the consistency checks we need read access to the database.

(i.e. quote from the same article ….{}These logical consistency checks cross check the internal index table of the index object with the user table that it is referencing. To find outlying rows, an internal query is constructed to perform a full intersection of the internal and user tables…){}

 

I have also reviewed the stack dumps and found error 976 which also confirms that we are trying to read the data.

Definition:

976 - 14
The target database, '%.*ls', is participating in an availability group and is currently not accessible for queries. 
Either data movement is suspended or the availability replica is not enabled for read access. 
To allow read-only access to this and other databases in the availability group, 
enable read access to one or more secondary availability replicas in the group.
For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.