Post reply

DBCC CheckDB

  • February 2, 2012 at 5:56 pm

    #250842

    So I've had my databases up and running for a while with no problems, then I read about database corruption and torn pages and started getting some pretty scary thoughts about what could happen if this happens to me.

    I've read that as a DBA, you should be using dbcc checkdb on your database on a weekly basis (if not more if time permits) to check for corruption and torn pages etc. Additionally, doing test restores from backups and then doing dbcc checkdb on those too.

    So what exactly does it do? When should I run it? Is it the same as the Check Database Integrity maintenance task that you can select when doing a maintenance plan? Is there any harm to my database if I simply run it?

    Thanks.

  • February 2, 2012 at 8:21 pm

    #1442178

    The check integrity maintenance task is indeed the same one.

    Wherever possible I try to run the check integrity task then take full backup and only after that I delete older backups.

    This way if the check integrity fails you do not delete older backups that might be good to restore. If you take a full backup of a database which will not pass a integrity check then you can not use that backup to restore properly.

    But if the database is very large then the check integrity task will run for a long time so in some cases I run the check integrity task only once a week.

    Blog
    http://saveadba.blogspot.com/

  • February 2, 2012 at 9:08 pm

    #1442183

    Hi,

    You have everything here 😉

  • February 3, 2012 at 2:01 am

    #1442238

    If you want an automated script, you could try this one[/url].

    -- Gianluca Sartori

  • February 7, 2012 at 6:20 pm

    #1444248

    I opened up your script Gianluca Sartori, but it is giving me some errors. I noticed that when you are creating the ##DBCC_OUTPUT table, you don't include the RowId field or outcome field, but when you are selecting from it, you are calling those fields. Hence I'm getting an error.

    Is this the complete script? Am I running it incorrectly?

  • February 8, 2012 at 4:58 am

    #1444429

    Draelith (2/7/2012)

    I opened up your script Gianluca Sartori, but it is giving me some errors. I noticed that when you are creating the ##DBCC_OUTPUT table, you don't include the RowId field or outcome field, but when you are selecting from it, you are calling those fields. Hence I'm getting an error.

    Is this the complete script? Am I running it incorrectly?

    Both RowId and Outcome are added explicitly with ALTER TABLE.

    What errors are you getting?

    -- Gianluca Sartori

  • February 9, 2012 at 6:37 pm

    #1445361

    It was giving me the little red squiggly lines... but yeah, it parsed fine. Now I just have to run it. I also changed your profile and recipient parameters to be hard coded in the procedure as it will only be used by me, so I felt it was easier this way.

  • February 10, 2012 at 2:15 am

    #1445404

    Glad you sorted it out.

    -- Gianluca Sartori

  • February 10, 2012 at 4:30 am

    #1445455

    Draelith (2/9/2012)

    It was giving me the little red squiggly lines...

    Edit -> Intellisense -> Refresh Local Cache.

    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 9 posts - 1 through 8 (of 8 total)

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