Shrink Log File in all databases

09 Sep

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 ‘checkpoint’

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

Leave a comment

Posted by on September 9, 2011 in SQl server Administration


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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