Home » Script to switch a whole SQL Server instance to SIMPLE recovery

Script to switch a whole SQL Server instance to SIMPLE recovery

by Vlad Drumea
0 comment

This brief post contains a script that can help switch a whole SQL Server instance, model and all user databases, to SIMPLE recovery.

The script is useful in case of dev/test/QA/UAT instances that have been left by accident to use the default FULL recovery model, yet do not have or need transaction log backups.

If you want to check if that’s the case with your non-prod instance, you can simply run this query:

Result set for above query |name|recovery_model_desc| |model|FULL| |DBATools|FULL| |StackOverflow2010|FULL| |AdventureWorks2019|FULL| SEO switch SQL Server simple recovery

And, if you want to check the size of your transaction logs, you can use the script in this post.

The script

You can also find it as SwitchInstanceToSimpleRecovery.sql in my SQL Server Scripts GitHub repo.

What it does

First it sets the model database to SIMPLE recovery.
This ensures that all future databases created on the instance will use the SIMPLE recovery model instead of FULL.

Then it goes through each user database that’s currently set to use the FULL recovery model, sets it to SIMPLE, issues a CHECKPOINT, and finally shrinks the transaction log.

Things to keep in mind

Shrinking large transaction log files is a resource intensive operation, so be mindful of when you run this.

If you have active transactions when running this, the transaction log for that specific busy database might not be shrunk as much as you’d expect. You’ll most likely have to run a shrink manually when the previous transactions have completed.
Or use a script to kill all the SPIDs with active transactions.

The script tries to reduce the transaction logs to the smallest size possible. If you need specific transaction logs to be at a certain size, you’ll have to resize them afterwards.


Don’t let your non-prod environments catch you off-guard with transaction logs filling up drive space.

You may also like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.