Problem 

If you have been a SQL DBA for a while, then you’ve likely been in a situation where developers have access to restore databases in dev and test environments. If not, you may ask “What is wrong with this? Where are you going with this Josh.” You see, whenever you restore a database in SQL Server it restores it using the full recovery model as default. Most developers don’t take the time to switch the databases to the simple recovery model after the restore, which can lead to the following.

• Transaction log grows rapidly filling up storage
• Potential for poor performance due to high VLF counts
• Unhappy DBAs who receive storage alerts in the middle of the night

From personal experience, the last bullet point above is one that I try to avoid at all costs.

Disclaimer

This content is for educational and informational purposes only. You are fully responsible for your SQL Server databases, so be sure to take extreme caution when taking action in production environments.

The post may also contain referral links and I earn a small commission if you choose to use them at no additional cost to you. In some cases, I have locked in exclusive deals for you.

Solution

So this isn’t a huge problem, right? We can simply switch the recovery model back to simple in SSMS and then shrink the LDF to release the physical space on disk, right? Yes, but what if the SQL Server instance has 15+ databases on it? It can get immensely time consuming to manually switch each database back to the simple recovery model every time a developer refreshes databases in dev or test.

I decided to write this blog post because I recently came across this issue working for one of my Upwork clients. The SQL Server instance had around 15 databases and they were refreshed often by developers. I logged onto the dev server last week and the transaction log drive had around 500 MB of available storage.

It was time to put my DBA powers to use and come up with an automated solution. After scrolling through tons of articles and customizing code to fit my environment, I came up with the following stored procedure which does the following.

• Loops though every database on the SQL Server instance, excluding system databases
• Sets the recovery model to Simple recovery
• Prints the T-SQL statements to SSMS

Please keep in mind that I am running this in a dev environment, NOT PRODUCTION. I would never recommend running this on a Production box during business hours unless absolutely necessary due to an emergency. Below is the code for the stored procedure.

Agent Job

You can simply automate the process and execute the stored procedure on a schedule via a SQL Server agent job. Using my client as an example, I created a SQL Server agent job to execute the stored procedure every 4 hours in their dev environment. I initially scheduled it to run once per day sometime around 12 am; however, once per day wasn’t enough and I often receive storage alerts from large transaction log files.

The following code will create a SQL Server agent job that runs once per day at 12 am. Please note that you will need to create the stored procedure from the previous code block before execute the code below.

Verify

After executing the stored procedure, you can run the T-SQL below to verify the recovery model change. This will return the database name and recovery model for all user databases.

YouTube Channel Hits 33,000 Subs!

To be alerted when I upload new YouTube content, subscribe to the channel and turn on all notifications. I upload freelancing, personal finance, and tech content weekly. Drop a comment below with any questions, feedback, or new videos that you would like to see on my channel. Thanks for stopping by and I will see you in the next post!