— New online course!  Getting Started with Azure DevOps — Special launch price 50% off —

Truncate and Shrink an Overgrown SQL Server Log File

by

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


2 Responses to "Truncate and Shrink an Overgrown SQL Server Log File"
  1. I see a lot of people using the change of recovery mode as a workaround for what appears to be a bug that stops the shrink opreation from working. Any chance of a fix MS?

    Changing the recovery mode is BAD BAD – especially if you’re considering restores / log shipping etc.

Leave a Reply

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

%d bloggers like this: