Post reply

Recovery model for a database

  • August 27, 2008 at 12:39 am

    #77077

    Hello,

    I have a database for which i need to see the recovery model to "simple". I did the following:

    Right click on the database name, select Properties, select the Options tab and select recovery model from the drop-down list. Selecting OK will change the recovery model immediately.

    After that i ran a stored procedure which have a cursor looping in order to capture information from linked servers. The SP takes about 2 hours to run.

    However i notice that when the SP has run for about 40 mins, i checked the Recovery model again, i switched back to FULL.

    Note that i didnt do a CHECKDB within the loop.

    Is this normal?

    Thanks for your help

    Amit

  • August 27, 2008 at 1:52 am

    #863549

    Something within the loop (or someone else) must have changed the recovery model. SQL won't change recovery models on its own

    Look for ALTER DATABASE statements within the code you're running. CheckDB doesn't affect recovery models.

    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 27, 2008 at 3:15 am

    #863574

    Hi

    Thanks for your reply. I did check for alter statement, but nothing is there. The stored procedure does not alter anything but ,it does, insert thousands of records to the database.

    Also note that there has been no one altering the database.

    Anyone of you think the issue is with the log file space?(Currently the allocated size is about 69MB).

    Regards

    Amit

  • August 27, 2008 at 10:04 am

    #863839

    SQL does not have a mind of its own. It will not change a database's recovery model unless someone issues a command to do so.

    Check any jobs that run on the server, see if any of them have an Alter Database in. If it happens again, set up SQL Profiler to catch any Alter Database so that you can see where it's coming from.

    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 4 posts - 1 through 3 (of 3 total)

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