Azure DevOps, Scrum, & .NET Software Leadership and Consulting Services

Free course! Predicting the Future, Estimating, and Running Your Projects with Flow Metrics

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

SUBSCRIBE TO THE BLOG


2 responses to “Truncate and Shrink an Overgrown SQL Server Log File”

  1. sarath Avatar
    sarath

    the below script will work for SQL 2008

    Use <database Name>
    GO

    Alter Database <database Name> Set Recovery Simple
    GO

    Alter Database <database Name> Set Recovery Full
    GO

    DBCC SHRINKFILE (‘<log file name>’, 1)
    GO

  2. Simon Avatar
    Simon

    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

Your email address will not be published. Required fields are marked *

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