Alternative to DBCC CHECKDB

  • hI.

    DBCC CHECKDB Taking more time to complete.is there any way to check .

    Thanks,

    Arjun

  • Not really, you can run the other DBCC commands that make up DBCC CHECKDB independently though, overall it wouldn't save you any time:

    https://msdn.microsoft.com/en-us/library/ms176064.aspx

    MCITP SQL 2005, MCSA SQL 2012

  • You can offload the work to a second server, restore a backup of the production DB and check that. You can break CheckDB down and run its pieces, that's CheckAlloc, CheckCatalog and CheckTable on all tables, do them bit by bit over a couple days.

    How big is the DB in question?

    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
  • If you offload it to another server, you'll still want to run a PHYSICAL_ONLY check on the first server.

    "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

  • Grant Fritchey (5/13/2015)


    If you offload it to another server, you'll still want to run a PHYSICAL_ONLY check on the first server.

    Why?

    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
  • Because you can have physical corruption that doesn't get backed up or restored.

    "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

  • Grant Fritchey (5/13/2015)


    Because you can have physical corruption that doesn't get backed up or restored.

    How?

    I keep hearing this advice, but the problem is it doesn't bear out. Backups are a page-by-page extent-by-extent copy of the database. Any damage to a database file will either completely fail the backup (backup with checksum) or will be included in the backup and restored on the second server.

    Physical only check reads and checks every page and checks the allocation structures. Both of those are included in a full backup and will be in a restored copy.

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

    and http://www.sqlskills.com/blogs/paul/importance-of-how-you-run-consistency-checks/#comment-826

    Have you heard of any cases where CheckDB fails, the database is then backed up, restored and a CheckDB on the restored copy succeeds?

    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
  • GilaMonster (5/13/2015)


    Grant Fritchey (5/13/2015)


    Because you can have physical corruption that doesn't get backed up or restored.

    How?

    I keep hearing this advice, but the problem is it doesn't bear out. Backups are a page-by-page extent-by-extent copy of the database. Any damage to a database file will either completely fail the backup (backup with checksum) or will be included in the backup and restored on the second server.

    Physical only check reads and checks every page and checks the allocation structures. Both of those are included in a full backup and will be in a restored copy.

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

    and http://www.sqlskills.com/blogs/paul/importance-of-how-you-run-consistency-checks/#comment-826

    Have you heard of any cases where CheckDB fails, the database is then backed up, restored and a CheckDB on the restored copy succeeds?

    My source for that inclusion is Paul. When I talked about offloading DBCC checks to the compressed backups that one of the RG tools had, he advised me to be sure to also do the physical checks on the original.

    "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

  • Grant Fritchey (5/13/2015)


    GilaMonster (5/13/2015)


    Grant Fritchey (5/13/2015)


    Because you can have physical corruption that doesn't get backed up or restored.

    How?

    I keep hearing this advice, but the problem is it doesn't bear out. Backups are a page-by-page extent-by-extent copy of the database. Any damage to a database file will either completely fail the backup (backup with checksum) or will be included in the backup and restored on the second server.

    Physical only check reads and checks every page and checks the allocation structures. Both of those are included in a full backup and will be in a restored copy.

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

    and http://www.sqlskills.com/blogs/paul/importance-of-how-you-run-consistency-checks/#comment-826

    Have you heard of any cases where CheckDB fails, the database is then backed up, restored and a CheckDB on the restored copy succeeds?

    My source for that inclusion is Paul. When I talked about offloading DBCC checks to the compressed backups that one of the RG tools had, he advised me to be sure to also do the physical checks on the original.

    I dropped him a mail asking for clarification

    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
  • It's not possible for there to be corruption in a database that doesn't get included in a backup - so I wouldn't have said that.

    If you're offloading DBCC CHECKDB to another server, you don't need to do any DBCC checks on the primary server.

    But there may have been some other reason I said to run physical_only on the primary server. I can't think of one off the top of my head though.

    Grant - do you have the email thread still?

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Paul Randal (5/13/2015)


    It's not possible for there to be corruption in a database that doesn't get included in a backup - so I wouldn't have said that.

    If you're offloading DBCC CHECKDB to another server, you don't need to do any DBCC checks on the primary server.

    But there may have been some other reason I said to run physical_only on the primary server. I can't think of one off the top of my head though.

    Grant - do you have the email thread still?

    Nope. Sorry Paul. It was a long conversation we had at MVP Summit about... 3 years ago (might have been 4, I'm getting old). It was when RG had released the tool that let you mount a backup as if it were a database. I don't recall why you told me to include the physical_only checks, just that you did. Sorry I can't be more helpful on that.

    However, great information. I'm very happy to know this and I'll absolutely adjust my advice accordingly. Thanks!

    "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

  • Thanks Paul.

    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
  • I have been "testing" CHECKFILEGROUP Across our database that spans 5 different Filegroups and spreading the loads over 2 days.

    BOL mentions that it does not do CHECKCATALOG so I have been running that independently too

    Feasible idea guys?

  • Absolutely.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Thank you for confirming

Viewing 15 posts - 1 through 14 (of 14 total)

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