September 29, 2015 at 6:31 am
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
September 29, 2015 at 6:59 am
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.
September 29, 2015 at 7:02 am
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.
September 29, 2015 at 7:42 am
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
September 29, 2015 at 7:49 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply