locked
DBCC CHECKDB on Production Database RRS feed

  • Question

  • Hi All,

    I am planning to run the DBCC CHECKDB on AlwaysON secondary replica.

    Can anyone suggest best practices for running it?

    Regards,

    kccrga


    -kccrga http://dbatrend.blogspot.com.au/

    Wednesday, December 21, 2016 5:50 AM

Answers

  • Hi,

    Why only to run DBCC CHECKDB only on Secondary replica?

    It is recommend to run DBCC on both primary and all readable secondary replica. Just not for the sake of running DBCC but there are chances that automatic page repair may not be overwriting corrupted page on secondary replica because automatic page repair can not do much with File Header, Boot Page and GAM/SGAM/PFS pages. Every storage has its own characteristics and can produce physical page corruption and it should be detected on this system.



    Kindly mark the reply as answer if they help

    Wednesday, December 21, 2016 6:06 AM
  • Hi kccrga,

    >>The ESTIMATE ONLY command gave it requires 875 Gigs of space.

    The result is very conservative, you probably don’t need that much space. During my test, 6 Gigs of files stored in varchar(max) columns does take about 400mb from tempdb whereas WITH ESTIMATEONLY says 4100mb is needed.

    >>The database is used for storing documents which are most (.pdfs) with the datatype VARCHAR(MAX).

    I would say that’s the issue here, why not store documents in Filestream? 

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kccrga Tuesday, February 7, 2017 3:22 AM
    Monday, January 2, 2017 5:26 AM

All replies

  • Hi,

    Why only to run DBCC CHECKDB only on Secondary replica?

    It is recommend to run DBCC on both primary and all readable secondary replica. Just not for the sake of running DBCC but there are chances that automatic page repair may not be overwriting corrupted page on secondary replica because automatic page repair can not do much with File Header, Boot Page and GAM/SGAM/PFS pages. Every storage has its own characteristics and can produce physical page corruption and it should be detected on this system.



    Kindly mark the reply as answer if they help

    Wednesday, December 21, 2016 6:06 AM
  • Hi Kccrga,

    Since in availability group all replicas has its own copy of database(s), it’s possible that you might have database inconsistency issue on both primary and second replicas, in this case I would suggest you run DBCC CHECKDB against all replicas on a regular basis.

    For more information, please review this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 21, 2016 7:43 AM
  • The database size is around 1 TB. The ESTIMATE ONLY command gave an estimate of 875 Gigs of tempdb space.

    The database is used for storing documents which are most (.pdfs) with the datatype VARCHAR(MAX)

    Any suggestions to run CHECKDB on a database of this size?


    -kccrga http://dbatrend.blogspot.com.au/


    • Edited by kccrga Monday, January 2, 2017 5:22 AM
    Monday, January 2, 2017 2:43 AM
  • Hi kccrga,

    >>The ESTIMATE ONLY command gave it requires 875 Gigs of space.

    The result is very conservative, you probably don’t need that much space. During my test, 6 Gigs of files stored in varchar(max) columns does take about 400mb from tempdb whereas WITH ESTIMATEONLY says 4100mb is needed.

    >>The database is used for storing documents which are most (.pdfs) with the datatype VARCHAR(MAX).

    I would say that’s the issue here, why not store documents in Filestream? 

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kccrga Tuesday, February 7, 2017 3:22 AM
    Monday, January 2, 2017 5:26 AM