26 August, 2010
5 Comments
1 category
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
Tags: SQL ServerT-SQL
Category: Uncategorized
select [name] from sys.databases
should be
select [name] from sys.databases where name ‘tempdb’
EXTREMELY handy thank you!
Thank you so much, you saved me about an our of manual work clicking in MSM studio.
Thank you!!