Hack of the day: Shrink all log files in the instance

Before I proceed, I will add two disclaimers:

  1. This script is provided as-is for knowledge purposes. It is not a recommendation, or in any way intended for production usage. No warranty or guarantee is made about the correctness of the script. Use it at your own risk.
  2. We do not recommend using SHRINK operations on any production databases. This script is provided for demonstration purposes only and that too for test or development servers. For more details, please refer to the series of posts by Paul Randal at http://www.sqlskills.com/BLOGS/PAUL/category/Shrink.aspx

With that behind us, here’s what I would like to share. On my laptop I keep running large queries and many times those will grow the log file. Though my database is in simple recovery mode, a single long running transaction can cause this kind of growth.

So I would sometimes like to reclaim disk space (oh so precious on a laptop!) periodically. I would however not like to shrink the data file, just the log file. (Shrinking data files can cause fragmentation issues to occur.)

The problem is I normally operate on like 5-6 databases on my laptop. I would not want to use the GUI nor script this manually each time. So here is a simple script-generator, which will generate the necessary DBCC SHRINKFILE commands, which you can then execute as per your requirement.

drop table #f
create table #f (name sysname, fileid int, filename sysname, filegroup sysname null,
size sysname, maxsize sysname, growth sysname, usage sysname)
exec sp_MSforeachdb ‘declare @s varchar(8000); use ?;
truncate table #f; INSERT #f (name, fileid, filename, filegroup, size, maxsize, growth, usage )
exec sp_helpfile
select @s = ”use ?; DBCC SHRINKFILE (” + name + ” )” from #f where usage = ”log only”
print @s ‘

Really crude script, but it does save me some time once in a while.


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 )

Connecting to %s

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