Tracking Recovery Models

  • Hi Guys,

    Is there anyway of tracking the when a recovery model changes?

    I have tried to create a trigger on the sys.databases view but it is not allowed.

    Our recovery models on about 5 database mysteriously changes to simple, so as you can imagine we had some sports with disk space.

    Any suggestions?

    Thanks

  • crazy_new (9/29/2015)


    Hi Guys,

    Is there anyway of tracking the when a recovery model changes?

    I have tried to create a trigger on the sys.databases view but it is not allowed.

    Our recovery models on about 5 database mysteriously changes to simple, so as you can imagine we had some sports with disk space.

    Any suggestions?

    Thanks

    While somewhat brute-force, I would think you could set up an Agent job to run maybe once a day, and record the current databases ,their recovery models, and the date/time it ran, to a table, then check that.

    Fairly quick and easy to setup, lighter footprint than something like a server-side trace, and easier to setup than an extended events session (which likely would also work, and could likely capture more information.)

    Hmm. Maybe look at doing an XE session as well, or instead of the Agent job.

  • I take it back on the Extended Events. In a quick check, I didn't see anything that looked like it would catch a change in recovery model.

  • Thanks, I will set up an agent job checking that then, but then I won't be able to see who changed it, I will only be able to see that it changed. At least that way I won't have the giant log file problem again...thanks for the feedback

  • It's logged to the error log.

    Maybe a server-scoped DDL trigger can catch the ALTER DATABASE?

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

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