Post reply

dbcc check*

  • December 30, 2011 at 8:27 pm

    #265705

    hi ,

    how often we use dbcc check commands in real time and what are the regular commands we use??/

    can any one explain about this.

    thanks in advance:-)

  • December 31, 2011 at 1:37 am

    #1427188

    Interview Question? 😀

  • December 31, 2011 at 9:25 am

    #1427226

    Doesn't feel like one.

    Why do you need to know this? The real answer depends on your backup sequence and how long you retain them. But then again if you can run it more often then it's good too!.

  • December 31, 2011 at 10:15 am

    #1427228

    you only need to run dbcc checkdb on a regular basis, as all the other check commands are a subset of this one command (checkalloc, checktable etc).

    so this one command will do it

    dbcc checkdb with all_errormsgs, no_infomsgs

    and that in fact is what the maintenance plans run.

    As Remi says, the best rule of thumb is to run it as often as you retain quickly recoverable backups

    ---------------------------------------------------------------------

  • December 31, 2011 at 10:37 am

    #1427232

    george sibbald (12/31/2011)

    you only need to run dbcc checkdb on a regular basis, as all the other check commands are a subset of this one command (checkalloc, checktable etc).

    so this one command will do it

    dbcc checkdb with all_errormsgs, no_infomsgs

    and that in fact is what the maintenance plans run.

    As Remi says, the best rule of thumb is to run it as often as you retain quickly recoverable backups

    And if you can handle that, do a restore to confirm your backups work and then run checdb on that. This allows you to run it daily without using the prod server's ressources.

    I do it both on the full backups as well as previous full + all log backups. That way I'm 100 000% sure I can restore to any PIT as far back as I need.

  • December 31, 2011 at 10:58 am

    #1427236

    george sibbald (12/31/2011)

    you only need to run dbcc checkdb on a regular basis, as all the other check commands are a subset of this one command (checkalloc, checktable etc).

    With the exception of check constraints (which is the one thing checkDB doesn't run). But, unless you do weird things to the DB, that's not something that should need running too often.

    As for checkDB, my general rule is often enough so that if you find corruption recovering from a clean backup is always an option. Hence, if all backups (full and log) are retained for a week, checkDB needs to be run at least once a week. On VLDBs (and by very large I don't mean 100GB, I mean above a TB), CheckDB can be split up into its constituent parts and run bit by bit (assuming you're not running it on a restored backup), and in that case the intervals need to be carefully selected with consideration of the backup retention so that recovering from a backup is still always an option.

    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

  • December 31, 2011 at 12:32 pm

    #1427245

    GilaMonster (12/31/2011)

    george sibbald (12/31/2011)

    you only need to run dbcc checkdb on a regular basis, as all the other check commands are a subset of this one command (checkalloc, checktable etc).

    With the exception of check constraints (which is the one thing checkDB doesn't run). But, unless you do weird things to the DB, that's not something that should need running too often.

    nice reminder. I had forgotten about that option. See, this year wasn't a complete waste of time!

    and now its party time!

    ---------------------------------------------------------------------

Viewing 7 posts - 1 through 6 (of 6 total)

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