Post reply

DBCC CheckDB on large database in HA

  • December 1, 2015 at 11:42 am

    #302562

    We have three servers in HA. The largest database that is part of HA is about 6 TB.

    Server 1 : Primary (Synchronous commit)

    Server 2 : Secondary (Synchronous commit)

    Server 3 : Secondary with backup preference (Asynchronous commit)

    Running DBCC CheckDB WITH PHYSICAL_ONLY takes about 3 hours.

    Running DBCC CheckDB takes about 15 hours.

    Is it sufficient to run PHYSICAL_ONLY on Server 1 and 2, then run the full DBCC CheckDB only on Server 3?

    Is there any reason to get a full DBCC CheckDB on Server 1 and 2?

    Thanks for any advice on the proper way to handle this with HA.

  • December 1, 2015 at 12:12 pm

    #1843298

    If you really want to be sure that there is no corruption, you will need to execute DBCC Checkdb on all the three servers. Just search for dbcc checkdb on Sqlskills.com, Paul has many good articles, make sure you go through the comments section, most likely your question is asked by someone else already.

  • December 1, 2015 at 12:15 pm

    #1843300

    here's the article i have bookmarked from Paul Randal, as mentioned above.

    http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-consistency-checking-options-for-a-vldb/

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 1, 2015 at 12:24 pm

    #1843305

    No, it's not.

    Running CheckDB on Server 3 tells you about the integrity of the DBs on server 3 only. The IO subsystem doesn't write to the transaction log when it mangles a page (and corruption is majority of the time a bad IO subsystem) and so any corruption on Server 1 will not be transferred to the other servers, hence running checkDB on the spare server tells you only about the state of the spare server.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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