Change recovery model for all SQL server database to Simple, truncate and shrink log files with T-SQL

When you have many databases on a database development SQL server and these databases are re-creatable, you can save space by setting the recovery model for all databases to Simple, truncate and shrink the log files, with the following T-SQL script

/*
Only use this script for SQL Server development servers!
Script must be executed as sysadmin
This script will execute the following actions on all databases
- set recovery model to [Simple]
- trucate log file
- shrink log file
*/
use [master]
go
-- Declare container variabels for each column we select in the cursor
declare @databaseName nvarchar(128)
-- Define the cursor name
declare databaseCursor cursor
-- Define the dataset to loop
for
select [name] from sys.databases
-- Start loop
open databaseCursor
-- Get information from the first row
fetch next from databaseCursor into @databaseName
-- Loop until there are no more rows
while @@fetch_status = 0
begin
print 'Setting recovery model to Simple for database [' + @databaseName + ']'
exec('alter database [' + @databaseName + '] set recovery Simple')
print 'Shrinking logfile for database [' + @databaseName + ']'
exec('
use [' + @databaseName + '];' +'
declare @logfileName nvarchar(128);
set @logfileName = (
select top 1 [name] from sys.database_files where [type] = 1
);
dbcc shrinkfile(@logfileName,1);
')
-- Get information from next row
fetch next from databaseCursor into @databaseName
end
-- End loop and clean up
close databaseCursor
deallocate databaseCursor
go

Leave a Reply

Your email address will not be published. Required fields are marked *

5 comments

  1. Joe P says:

    select [name] from sys.databases

    should be

    select [name] from sys.databases where name ‘tempdb’

  2. Eugene Booyens says:

    EXTREMELY handy thank you!

  3. […] Below is a slight variant of some TSQL I borrowed from https://www.roelvanlisdonk.nl/?p=1703 […]

  4. Sigitas says:

    Thank you so much, you saved me about an our of manual work clicking in MSM studio.

  5. Sven says:

    Thank you!!