Shrinking SQL Server Log Files

This script performs a truncate log operation followed by a shrink log file operation.

First it selects the current database name for the parameter of the BACKUP LOG command. Then the filename of the log file of the current database is retrieved to pass it to the SHRINKFILE command.

select * from sysfiles

declare @s nvarchar(100)

print 'truncating ' + db_name()
set @s = 'backup log ' + db_name() + ' with truncate_only'
exec (@s)

select @s = rtrim(name) from sysfiles where groupid = 0
print 'shrinking ' + @s
set @s = 'dbcc shrinkfile (' + @s + ', truncateonly)'
exec (@s)

select * from sysfiles

1 thought on “Shrinking SQL Server Log Files

  1. Pingback: Shrinking all online Database Files « devioblog

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 )

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.