Running DBCC CHECKDB Against Mirrored DB

Question

Running DBCC CHECKDB Against Mirrored DB

wak_no1

SSCrazy

Points: 2400
More actions
November 28, 2013 at 2:40 am

#395909

Hi
Would anyone happen to know how I would run a full DBCC CHECKDB against the database that is acting as a Mirror?
Would I need to suspend Mirroring, take the database out of restoring mode, run the check, put the db back in restoring mode and then restart the mirroring session? Would this work?
I need to run urgently as we had issues with Mirrroing last night where it became suspended, the logs suggest a DBCC checkdb be run ASAP.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

wak_no1 (11/28/2013)
Would I need to suspend Mirroring, take the database out of restoring mode, run the check, put the db back in restoring mode and then restart the mirroring session? Would this work?

That would not work. Once you recover the database you cannot put it back in the restoring state and restart mirroring.

Only way you can do this without breaking and having to re-init the mirroring is by failing the mirroring over, so what was the mirror is not the principal, run checkDB on the new principal and once complete fail the mirroring back.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wak_no1 SSCrazy Points: 2400 More actions · Answer 2

Dammit, this was what I didn't want. Oh well.

thanks for advice.

lalit.asky SSC Journeyman Points: 86 More actions · Answer 3

Just create a database snapshot of the mirrored database and run checkdb on the snapshot.

quackhandle1975 SSChampion Points: 11055 More actions · Answer 4

Just a heads up:

http://www.sqlskills.com/blogs/paul/issues-around-dbcc-checkdb-and-the-use-of-hidden-database-snapshots/[/url]

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Tac11 SSCertifiable Points: 6980 More actions · Answer 5

Is running DBCC CheckDB is safe on 'Snapshot' of mirrored database on Mirror server? I think running 'Checkdb' would need lots of TEMPDB resources. What should be safe side running on the 'snapshot' and what need to watch for?

quackhandle1975 SSChampion Points: 11055 More actions · Answer 6

Tac11 (1/22/2015)
Is running DBCC CheckDB is safe on 'Snapshot' of mirrored database on Mirror server? I think running 'Checkdb' would need lots of TEMPDB resources. What should be safe side running on the 'snapshot' and what need to watch for?

This excellent article should give you all the info to make CHECKDB run faster.

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 7

What's safe? CHECKDB does use resources, and if that's an issue for your mirror server, you might see performance issues. However this won't cause issues with the ability of SQL Server to write to the mirror or snapshot

lalit.asky SSC Journeyman Points: 86 More actions · Answer 8

The question was , how to run DBCC CHECKDB on a mirrored database and this is the best possible answer I feel(other than a failover). DBCC CHECKDB itself is resource intensive and that is the reason SQL server also uses multiple underlying snapshots to run CHECKDB on a database untill TABLOCK is specified. And snapshots do add a overhead in terms of copying the first time changed pages from the orginal database to the snapshot, and if it is done for a mirrored database, it may affect the DML response on primary database in a synchronous setup. even Microsoft advices not to create multiple snapshots of a database.

Running checkdb on a snapshot may have certain restriction too.