Post reply

What can set the recovery model of a database?

  • May 8, 2023 at 3:57 am

    #4185810

    Hi

    I have had an incident where a database log has grown to a large size. Normally I would backup the logs however the recovery model has been set to SIMPLE. I am under the assumption that only a human can set this option.

    I have checked the logs and cannot find any alter database statements.

    Has anyone had an experience of the database recovering and setting the recovery model to SIMPLE?

     

    Cheers

     

    Jamie

    We are the pilgrims, master.
    We shall go always, a little further.

  • May 8, 2023 at 1:17 pm

    #4186095

    Jamie Ingram-729524 wrote:

    Hi

    I have checked the logs and cannot find any alter database statements.

    Think about that statement... Do you have any proof that the database was ever set to anything other than SIMPLE?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 8, 2023 at 1:25 pm

    #4186100

    Someone has to set the recovery model of the database. They don't change randomly. Now, if the full backup of a database was in a different recovery model than the database is currently, and you ran a restore, then, in that case, you'd see the recovery model "change", but not really. It's the model that was backed up, so it's the model that gets restored.

    Hope that helps.

    "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

  • May 8, 2023 at 1:29 pm

    #4186102

    Default trace holds the information you seek ! ( as long as it did not get cleaned up after a number of rolled forward for the xe files )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • May 8, 2023 at 5:41 pm

    #4186210

    Go to DB Properties. What is the last log backup date ?

    I just changed a FULL DB to SIMPLE and it  still shows the last Log backup date after I  changed recovery model.

    Another DB that has always been SIMPLE doesn't have a log backup date.

    • This reply was modified 1 year, 6 months ago by  homebrew01.

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

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