[dba-SQLServer] Quick trick to drastically reduce Log file size

Arthur Fuller fuller.artful at gmail.com
Fri Feb 8 18:54:04 CST 2008


While beta-testing a great forthcoming Red Gate product called Data
Generator, I built several test databases with many millions of rows in
their tables. After the product populated the databases, I had several
whopping log files, approximately 60 GB each. DBCC Shrink is *not* the
answer in such a situation.

If you deem the contents of the log to be useless, or alternatively, if
you've backed up a huge log file and merely want to reclaim the disk space,
there is a very quick and slick solution. If you already know it, fine. If
you don't, this tip might prove useful to you.

1. Open an Explorer window and also Management Studio. Navigate the Explorer
window to the directory in which you house your log files. You may find it
useful to sort the listing by file size descending.
2. In Management Studio, detach the database(s) of interest (those with huge
log files).
3. Switch to the Explorer window and delete the corresponding log file(s).
4. Switch back to Management Studio, right-click on the Databases node and
select Attach. Choose the database of interest.
5. The Attach Database dialog will display both the data and the log files
in the lower panel. Select the log file and click Remove.
6. Click OK and Studio attaches the database, creating a brand-new log file
automatically, about 1MB in size.

In my case, I just got back about 120GB in a couple of minutes.

hth someone,
Arthur



More information about the dba-SQLServer mailing list