AlwaysON - HADRON Learning Series - Running DBCC On A Secondary

HADRON allows DBCC to be executed directly against a secondary replica.  The DBCC can be run online, as is, or with TABLOCK if the HADR activity is suspended so the DBCC can acquire the database lock necessary to support the TABLOCK option.

A secondary that allows connections enables the administrator to execute DBCC CHECKDB.   The log blocks are being shipped and redone on the secondary so the DBCC is able to execute as if it was being run on a  primary replica.   The DBCC can be executed in one of two ways, ONLINE or with TABLOCK.

ONLINE is the most common as it does not require the HADR activity to be suspended in order to execute.   Online DBCC works just like you are used to the online DBCC today.  It creates an internal snapshot and performs copy-on-write activity in order to check a specific point-in-time while allowing redo to progress.  The difference when running it on the secondary is that the point in time on the secondary replica may be behind the primary based on your synchronization settings and capabilities.

To avoid the internal snapshot the DBCC can be executed with TABLOCK.  In order to allow DBCC checkdb to obtain the proper database lock you must first suspend the HADR activity on the database.  Run the DBCC checkdb(MyDb) with TABLOCK and then resume the HADR activity.   It goes without saying that suspending the HADR activity can lead to a backlog of log blocks and cause the database log file(s) to grow on the primary.

Command Examples

DBCC CHECKDB(MyDb)

ALTER DATABASE MyDb SET HADR SUSPEND
DBCC CHECKDB(MyDB) with TABLOCK
ALTER DATABASE MyDb SET HADR RESUME

Bob Dorr - Principal SQL Server Escalation Engineer