Truncate and Shrink an Overgrown SQL Server Log File

I run my Team Foundation Server inside of Virtual Server 2005 R2.  This morning I noticed that the virtual server’s disk usage had gotten over 50GB.  That seemed like kind of a lot. 

Part of it was a bunch of nightly database backup files for TFS that had accumulated on the virtual disk.  That was 9 GBs.  (Hint: you should be doing a nightly backup of your TFS SQL Server.)

Something was still using a lot of disk space.

I looked at the data files and log files for SQL Server (C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData).  The TfsActivityLogging database’s log file was 30GBs. 

Looking around on google, I couldn’t find a straight-forward SQL script on how to truncate and shrink my SQL Server log file.

Here’s the script:

use TfsActivityLogging

backup log TfsActivityLogging
with truncate_only

dbcc shrinkfile (TfsActivityLogging _log, 1)

In case you’re wondering what the next steps are for shrinking the 50gb virtual hard disk:

1. From the virtual windows machine, run the disk defrag utility
2. Go into the virtual server definition for the virtual machine and mount the precompact.iso
3. From the virtual windows machine, run precompact.exe
4. Shutdown the virtual machine
5. In Virtual Server, “inspect” the virtual machine’s hard disk then choose “compact virtual hard disk”.

-Ben