Knowledgebase

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