Today there was an interesting question posted on twitter, and that made me think about ways on how we can play around with SQL Server features/options to find solutions for tricky questions and requirements.

There are multiple ways to find this info, and the first and foremost thing which came on my mind was to read the error logs.

1. We can use sp_readerorrlog procedure to pull the details easily, however this details will going off when the error logs are recycled.

Sp_readerrorlog 0, 1, 'recovery'

The above method is great when you dont need huge amount of historical data and you are looking only for a recent specific time frame.

2. The second option is to use the default trace to find this info, however this is very tough as the recovery model changes are tracked under event class 164 which is the one for object_changes.There is no specific info which says the recovery model was changed.

A good post which leverage default trace + sp_readerorrlog + sp_enumerrorlogs is written by Simon Sabin(B/T) is available here.

In case you are using SQL 2008/above and would like to track this from now on then you also can leverage PBM(Policy Based Management) for this purpose.

The idea about PBM didn’t came to my mind early on, however it eventually came and I decided to test it to see if we can track historical info on recovery model changes.

A policy was created for all the databases with evaluation mode was ‘On Schedule’

The schedule was set for every 1 minute to ensure that multiple recovery model changes are evaluated and not missed.

The condition was also created as

The condition@RecoveryModel = Full is just one value for creating a condition, so that we can evaluate the policy.

Initially the database for this test case Adventureworks2012 was running Full recovery model and I changed it to Simple, Full and then Bulk Logged.

This means that I changed recovery model 3 times and currently its running on Bulk logged recovery model.

If we want to evaluate the policy manually we can do that too.if we opt to do a manual evaluation then we will get the evaluation details which will clearly says that database Adventureworks2012 is having a different recovery model than what is mentioned in the policy.

Our policy evaluation is running in the back ground to ensure that manual intervention is not required and we don’t miss any recovery model changes which might happen frequently (Multiple changes between 1 min can be missed out)

Note – Running schedules for every 1 min is not really recommended and should be used only if there is a specific requirement.

The tracking of recovery model changes comes in under the View History option available under Policy Management.

The first policy deviation was when the database was set as Simple recovery model.If we click the hyperlink under Detail section we will get the details of actual value and expected value.

The second policy evaluation success message is when the recovery model was changed to Full.

The third policy deviation was when the database was set as Bulk logged recovery model.

This way we can track the recovery model changes and we can disable/delete this policy once the requirement is completed.

The total number of days to retain this history for all policies is available too.It would have been great if we could retain history per policy,however that option is not available at this moment.

Conclusion 

As a hard and fast rule I definitely won’t allow anyone to change the database recovery model option. I can definitely enforce a policy to restrict it too, however as the requirement of the user was to track the changes I mentioned couple of ways to deploy it.

I am interested to know if you have dealt any similar situation like this. Feel free to pass on your experience as comments.

Thanks for reading.