RSS

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]
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’)

checkpoint

Print ‘checkpoint’

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 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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s