Post reply

''Repair minor problems if possible''?

  • November 28, 2005 at 10:39 pm

    #364331

    Starting a new thread...

    Cindy Hallum posted:

    I have a DB Maintenance Job set up and I chose to "repair minor problems if possible" in the database integrity job step. If it does have to do a repair, does it show up in the log?

    Thanks......Cindy

    My advice is always to turn this option off. In fact, in SQL Server 2005 I made sure the option was removed. There are several reasons for this:

    1) to run any repair option requires putting the database into single_user mode, thus taking it offline

    2) you should never automatically repair corruptions. You should always do root-cause analysis to find out what happened and then make an informed choice as to how to recover, including corrective steps to prevent a recurrence. The odds are any corruptions you encounter in regular jobs are h/w caused and would require the (aptly named) REPAIR_ALLOW_DATA_LOSS option to repair and so having this option set in your job would be a waste.

    Some questions for you:

    What frequency do you run checks?

    And backups? Do you test your backups are valid?

    Do you have a comprehensive disaster recovery strategy that you practise regularly?

    Regards

    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

  • December 1, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • January 5, 2006 at 7:00 am

    #612868

    Cindy's base question was:   "If it does have to do a repair, does it show up in the log?"

    I understand your thoughts on "automatic repairs" but what is the answer to her question?

    Thanks,


    Student of SQL and Golf, Master of Neither

  • January 5, 2006 at 10:51 am

    #612961

    The answer is yes. A CHECKDB summary of errors found/fixed is always put into the errorlog and if any errors are found, they will be logged in the Windows event log too.

    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

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

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