Post reply

Offloading DBCC CHECKDB to another server

  • November 10, 2021 at 12:15 pm

    #3949865

    Hello,

    we've decided to move CHECKDBs for our biggest databases to another server, separated from production instance, with full weekly checks on restored DBs.

    I assume it wouldn't be wise to cancel CHECKDB jobs on the production server completely. Are there any recommendations what minimum level of CHECKDB should we should still use on the production server with minimum performance impact and stay on the safe side at the same time? I was thinking about PHYSICAL_ONLY option.

    Thank you in advance,

    Regards,

    Domen

    • This topic was modified 3 years ago by  domenm.
    • This topic was modified 3 years ago by  domenm.
    • This topic was modified 3 years ago by  domenm.
    • This topic was modified 3 years ago by  domenm.

  • November 10, 2021 at 3:16 pm

    #3949967

    There is a good article on checkdb here:

    https://www.mssqltips.com/sqlservertip/2399/minimize-performance-impact-of-sql-server-dbcc-checkdb/

    I do recommend you read that as it addresses your concerns with PHYSICAL_ONLY as well as using the backup-restore-checkdb approach.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 14, 2021 at 4:58 pm

    #3952914

    Hello Brian,

    thank you very much for your reply.

    I think I understand the DBCC CHECK principles, at least the basics :).  So, in regard of performing full CHECKDB in separate server, I'm sure we are on the right track, since some of our database wouldn't allow us to do this on the production instance. There's too much of performance impact and it takes too long to complete the check.

    My question was, what kind of DBCC CHECKDB we would still need to do on the production instance in case we perform full CHECKDB on restored database on different server. So, basically I was asking for some good practice advices from DBAs that have experiences with this.

    So, we currently do the following:

    1. PHYSICAL_ONLY on production databases

    2. FULL CHECKDB on restored DBs

    But is this enough?

    Thanks,

    Domen

     

  • November 15, 2021 at 2:59 pm

    #3953175

    I would go with that approach UNLESS the full CHECKDB fails on the restored DB.  If it fails, I would then run a full on prod as well to ensure that the problem is with prod and not that the test system disk is starting to fail.

    If I had a downtime window though, I would still do a full checkdb on prod, but that is just for my sanity, not because of any requirements that I am aware of.

    Only thing with running the physical only is that it has a performance hit too.  IF that performance hit is too great, you may need to just skip checkdb on prod completely unless problems arise in the restored DBs.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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