locked
DBCC Checkdb on Production RRS feed

  • Question

  • Hello Team,

    I need to run consistency check (Checkdb) on 5 TB database , what are the basic prerequisite needs to be taken care.

    Does it require any downtime for DBCC?

    Br

    ChetanV

     

    Thursday, August 3, 2017 9:04 AM

Answers

All replies

  • >>>Does it require any downtime for DBCC?

    No

    Read Paul's great article

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


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 3, 2017 9:17 AM
    Answerer
  • >>>Does it require any downtime for DBCC?

    No

    Read Paul's great article

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


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    I am not satisfied with the answer , as per the link provided above is says :

    • It takes a long time to run (based on many factors – see my previous post here for details).
    • It uses lots of resources – memory, CPU, IO bandwidth, tempdb space.

    does it impact business ? so that maintenance can be taken by stakeholders , because without maintenance window we cannot take any risk to answer the reason for any outage.

    Br

    ChetanV 

    Thursday, August 3, 2017 11:17 AM
  • Sure that it takes resources, and thus you should run it where there is low or no activities on the server

    We run DBCC CHECKDB on 2AM for example. Moreover I would suggest you looking and adopt Ola's script running DBCC CHECKDB ...

    https://ola.hallengren.com/sql-server-integrity-check.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, August 3, 2017 11:29 AM
    Answerer
  • Yes, it takes resources, and if you are only running DBCC CHECKDB for routine testing, which you should, you should run DBCC CHECKDB on a restored copy of the database. This has two advantages:

    1) No impact on production.
    2) You also get to test your backups.

    As for the latter point, if there is corruption, you will not know whether the corruption comes from the source database, or from the BACKUP/RESTORE process. But you will know that you are in dire straits no matter what. If this happens, you may be better off running DBCC CHECKDB also in production. Although, if the output indicates that a specific table is the victim, you could let it suffice with running DBCC CHECKTABLE on that table.

    Obviously, it will take time and disk space to restore a copy of a 5TB. But that is nowhere close to the cost if you get corruption in the database and it is not discovered until several weeks later, and you longer have any fresh backup.

    So those are your alternatives:
    1) Run DBCC CHECKDB in production.
    2) Run DBCC CHECKDB on a restored copy.

    Not running DBCC CHECKDB is not an alternative.

    Thursday, August 3, 2017 2:01 PM
  • You may want to consider using the with physical_only option as well for performance reasons.

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

    Thursday, August 3, 2017 2:05 PM
  • I would also suggest physical_only option but please note that logical checks are omitted. Normally TB of databases use physical_only option and run checkdb twice a week with that option. And for complete checkdb like Erland suggested you can restore backup on test server and run checkdb there. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, August 3, 2017 2:55 PM