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]

-- 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
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
    print 'Setting recovery model to Simple for database [' + @databaseName + ']'
    exec('alter database [' + @databaseName + '] set recovery Simple')
    print 'Shrinking logfile for database [' + @databaseName + ']'
    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 loop and clean up
close databaseCursor
deallocate databaseCursor

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

  1. select [name] from sys.databases

    should be

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts