CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

AlwaysON - HADRON Learning Series - Running DBCC On A Secondary

AlwaysON - HADRON Learning Series - Running DBCC On A Secondary

  • Comments 5


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

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post
  • But running CHECKDB on the replica tells you nothing about the state of the database on the primary - just like running CHECKDB on a database mirror pre-Denali. And why would you *want* to run with TABLOCK instead of running online?

  • But running CHECKDB on the replica tells you nothing about the state of the database on the primary - just like running CHECKDB on a database mirror pre-Denali. But at least it lets you know the state of the replica. And why would you *want* to run with TABLOCK instead of running online?

  • Paul is fair in his comments that running DBCC on the secondary does not check the primary.  As he points out is does however, tell you that your secondary state and when you combine that with backup on the secondary you have a better strategy for making sure your backups are clean without placing the loads on the primary.   Another factor that can be overlooked, which CSS has seen, is that the reply of the log on the secondary can identify corruption on the primary.  

    For example we had issues with state reads on the primary database controller returning incorrect images of the page.  Because you are always running redo on the secondary it will catch a duplicate LSN issue.  

    Why you might use TABLOCK is generally disk space based.  If the DBCC takes some time and redo is dirting pages you have the same effect as running the DBCC online on the primary and the snapshot can grow.  So if it is a growth issue or a sparse file limitation issue you might also use the TABLOCK capabilities.

    Thanks Paul

  • Hi

    While it's good to know that I have now an "added" capability to run DBCC CHECKDB on HADR secondary (which has it's own benefits per Bob), however one of my primary task still is to get the consistency check done on PRIMARY. Needless to say with on a VLDB this takes very long?

    A simple question? with HADR can we do away running DBCC CHECKDB on PRIMARY and can rely on SECONDRY?

    Please advise!

  • Due to a change near release altering the secondary to a SUSPENDED state prevents read access to the secondary and the TABLOCK option will fail with the following message:

    Msg 7934, Level 16, State 1, Line 1

    DBCC CHECK cannot proceed on database MyDb because it is a Secondary Replica and either Snapshot creation failed or the WITH TABLOCK option was specified. Secondary Replica databases cannot be exclusively locked for DBCC CHECK. Reason may have been given in previous error.

    This same change is no different than if a secondary is not connected to a primary, the secondary prevents read only activities.    Microsoft is looking at lifting this restriction for readable, secondaries in a future release.

Page 1 of 1 (5 items)