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:
| 1 2 3 4 | SELECT [name], [recovery_model_desc] FROM [sys].[databases] WHERE [recovery_model] = 1; |
And, if you want to check the size of your transaction logs, you can use the script in this post.
The script
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | /*Set model to SIMPLE recovery*/ ALTER DATABASE [model] SET RECOVERY SIMPLE; GO /*Set the user databases to SIMPLE recovery*/ DECLARE @DatabaseName NVARCHAR(130), @LogFileName NVARCHAR(128), @SQL NVARCHAR(500), @LineFeed NVARCHAR(5); SET @LineFeed = CHAR(13) + CHAR(10); DECLARE SwitchToSimple CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT QUOTENAME([d].[name]), [f].[name] FROM sys.[databases] AS [d] INNER JOIN sys.[master_files] AS [f] ON [d].[database_id] = [f].[database_id] WHERE [d].[recovery_model] = 1 AND [d].[state] = 0 AND [f].[state] = 0 AND [f].[type] = 1; OPEN SwitchToSimple; FETCH NEXT FROM SwitchToSimple INTO @DatabaseName, @LogFileName; WHILE @@FETCH_STATUS = 0 BEGIN /*set database to SIMPLE recovery*/ SET @SQL = N'USE [master];' + @LineFeed + N'ALTER DATABASE ' + @DatabaseName + N' SET RECOVERY SIMPLE;' + @LineFeed; EXEC(@SQL); /*Run a checkpoint and shrink the transaction log file*/ SET @SQL = N'USE ' + @DatabaseName + N';' + @LineFeed + N'CHECKPOINT;' + @LineFeed + N'DBCC SHRINKFILE (N''' + @LogFileName + N''', 1);' + @LineFeed; EXEC(@SQL); FETCH NEXT FROM SwitchToSimple INTO @DatabaseName, @LogFileName; END; CLOSE SwitchToSimple; DEALLOCATE SwitchToSimple; |
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.
Conclusion
Don’t let your non-prod environments catch you off-guard with transaction logs filling up drive space.