Post reply

Optimal settings for DBCC checks

  • August 5, 2014 at 11:00 am

    #1735318

    Grant Fritchey (8/5/2014)

    sqldriver (8/5/2014)

    Grant Fritchey (8/5/2014)

    Wait a sec, if you're offloading DBCC checks, the one check you can't offload is PHYSICAL_ONLY. That one check must still be run on the original. Then, it's all the other checks, which you need to run, that can be run in a secondary machine to reduce the overhead on the original machine.

    I'm restoring from full backups and running the DBCC checks offloaded because running them on the production servers is not a palatable solution to management; other maintenance tasks take priority (index, statistics, full text catalog) because they impact user experience.

    This was the most win I could get out of the discussion. I'm trying to make the best of it.

    Thanks

    Right, but you still have to run the physical checks on the production server.

    Unless you're planning on hiring me, I can't implement that. That decision was made by the business since it can impact performance and user experience.

    And, yes, I explained that a database going boink would also impact performance and user experience.

  • August 5, 2014 at 11:03 am

    #1735321

    Grant Fritchey (8/5/2014)

    sqldriver (8/5/2014)

    Grant Fritchey (8/5/2014)

    Wait a sec, if you're offloading DBCC checks, the one check you can't offload is PHYSICAL_ONLY. That one check must still be run on the original. Then, it's all the other checks, which you need to run, that can be run in a secondary machine to reduce the overhead on the original machine.

    I'm restoring from full backups and running the DBCC checks offloaded because running them on the production servers is not a palatable solution to management; other maintenance tasks take priority (index, statistics, full text catalog) because they impact user experience.

    This was the most win I could get out of the discussion. I'm trying to make the best of it.

    Thanks

    Right, but you still have to run the physical checks on the production server.

    Shouldn't need to. The stuff that physical_only checks would be included in the backup (invalid checksums, torn page, bad headers) and so will be picked up in the CheckDB on the restored DB.

    Only extra thing I'd do is ensure that the full backups on prod are taken WITH CHECKSUM. That'll catch any 'cannot read this page' errors from the OS.

    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

  • August 5, 2014 at 5:34 pm

    #1735440

    GilaMonster (8/5/2014)

    Grant Fritchey (8/5/2014)

    sqldriver (8/5/2014)

    Grant Fritchey (8/5/2014)

    Wait a sec, if you're offloading DBCC checks, the one check you can't offload is PHYSICAL_ONLY. That one check must still be run on the original. Then, it's all the other checks, which you need to run, that can be run in a secondary machine to reduce the overhead on the original machine.

    I'm restoring from full backups and running the DBCC checks offloaded because running them on the production servers is not a palatable solution to management; other maintenance tasks take priority (index, statistics, full text catalog) because they impact user experience.

    This was the most win I could get out of the discussion. I'm trying to make the best of it.

    Thanks

    Right, but you still have to run the physical checks on the production server.

    Shouldn't need to. The stuff that physical_only checks would be included in the backup (invalid checksums, torn page, bad headers) and so will be picked up in the CheckDB on the restored DB.

    Only extra thing I'd do is ensure that the full backups on prod are taken WITH CHECKSUM. That'll catch any 'cannot read this page' errors from the OS.

    You're a braver soul than I am. I'd still feel it was necessary. I've seen too many instances of backups writing out crap pages. Most of those were logical problems though, not physical.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 5, 2014 at 5:47 pm

    #1735441

    This is like the time my dad told me I had to floss my teeth and my mom told me I had to use mouthwash and I basically ended up sleeping with a toothbrush under my pillow.

  • August 5, 2014 at 11:43 pm

    #1735485

    Grant Fritchey (8/5/2014)

    GilaMonster (8/5/2014)

    Grant Fritchey (8/5/2014)

    sqldriver (8/5/2014)

    Grant Fritchey (8/5/2014)

    Wait a sec, if you're offloading DBCC checks, the one check you can't offload is PHYSICAL_ONLY. That one check must still be run on the original. Then, it's all the other checks, which you need to run, that can be run in a secondary machine to reduce the overhead on the original machine.

    I'm restoring from full backups and running the DBCC checks offloaded because running them on the production servers is not a palatable solution to management; other maintenance tasks take priority (index, statistics, full text catalog) because they impact user experience.

    This was the most win I could get out of the discussion. I'm trying to make the best of it.

    Thanks

    Right, but you still have to run the physical checks on the production server.

    Shouldn't need to. The stuff that physical_only checks would be included in the backup (invalid checksums, torn page, bad headers) and so will be picked up in the CheckDB on the restored DB.

    Only extra thing I'd do is ensure that the full backups on prod are taken WITH CHECKSUM. That'll catch any 'cannot read this page' errors from the OS.

    You're a braver soul than I am. I'd still feel it was necessary. I've seen too many instances of backups writing out crap pages. Most of those were logical problems though, not physical.

    Well the backup (with checksum) will pick up the hardware errors (OS error x), any damaged pages will be included in the backup and checkDB on the restored copy will pick those up.

    The critical thing is if the backup ever gives errors or the checkDB on the restored copy ever gives errors, a full checkDB of prod MUST immediately be done.

    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 5 posts - 16 through 19 (of 19 total)

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