共用方式為

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

Comments

  • Anonymous
    December 16, 2010
    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?

  • Anonymous
    December 16, 2010
    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?

  • Anonymous
    January 06, 2011
    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

  • Anonymous
    August 21, 2011
    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!

  • Anonymous
    May 28, 2013
    The comment has been removed