<< Run Non-"Hello, World" VSTS WebTests As Part of a Team Build | Home | Programmatically Set AreaPath on a WorkItem >>

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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).  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

posted @ Monday, July 09, 2007 9:20 AM

Print

Comments on this entry:

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Peter at 7/12/2007 11:08 AM
Gravatar
How do you like Virtual Server? Do you use it for more than Team Foundation Server?

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Mohan Kirti at 8/11/2008 1:45 AM
Gravatar
I have sucussfully trancate the log files using given sricpt and my application is working.

Thanks...Mohan

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Shilpa at 10/20/2008 11:11 AM
Gravatar
Thanks Bejamin.. It worked!

-- Shilpa

I use http://www.mygoogle.co.in how about you?

# Handjob hetro.

Left by Handjob. at 10/30/2008 7:19 AM
Gravatar
Hetero handjob. Handjob gallery. Free handjob clips.

# Wild girls.

Left by Girls gone wild. at 11/4/2008 11:20 PM
Gravatar
Girls going wild. Girls gone wild.

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Amin at 12/1/2008 3:14 PM
Gravatar
I did use the script, it worked wonderfully!! However make sure if you cut and paste the script that not to include a space between TfsActivityLogging and _log, i.e it should look exactly as follows:
dbcc shrinkfile (TfsActivityLogging_log, 1)

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Theresa B at 12/2/2008 4:32 PM
Gravatar
No_log and Truncate_only have been discontinued in SQL Server 2008. Be careful when using these in 2005 because it will break the log chain. Heres a good article explaining this: http://mssqltips.com/tip.asp?tip=1464

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Ketan Kolge at 1/19/2009 2:56 AM
Gravatar
Thx. I will create a job and run it every 2 weeks. this should control the size of database as required.

-Ketan

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Irfan at 2/2/2009 4:14 AM
Gravatar
DBCC SHRINKFILE (<FileName>,1);
DBCC SHRINKDATABASE (<DBName>,1);
OR

DBCC SHRINKFILE (<DBFILE_ID>,1); (Which is "1" For DATABASE FILE and "2" For LOG FIle)

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Irfan Siddique at 2/2/2009 6:36 AM
Gravatar
BACKUP LOG <DataBase_Name>
WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (2,1, TRUNCATEONLY)
GO



-- For SHRINK DATABASE SIZE


DBCC SHRINKDATABASE (<Database_Name>, 0)




/* "2" For Log File_ID and "1" For Data FIle_ID
You can use Database File Name and log File Name)
*/

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Duane at 2/4/2009 11:19 AM
Gravatar
>>No_log and Truncate_only have been discontinued in SQL Server 2008. Be careful when using these in 2005 because it will break the log chain

If the above is true then how do you create a backup?

If I run a manual backup of the log with log truncation set, and compare the SQL to the maintenance plan "Backup Database" with the LOG Option set, the two are IDENTICAL except for one has REWIND and the other NOREWIND. The same thing is true when I create a database backup....

But, the database backup keeps growing (presumably with log data) when the maintenance plans run. So, what gives? why does the maintenance plan not drop the log data?

(btw: SQL 2005 Std, Current SP's and hotfixes installed.)

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by rose at 2/9/2009 6:38 AM
Gravatar
BACKUP LOG <DataBase_Name>
WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (2,1, TRUNCATEONLY)
GO


will it cause any problem with SQL server 2008?
can i proceed with this query to truncate and shrink the DB log file?


# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Tom at 3/10/2009 7:22 AM
Gravatar
Worked for me!


Thank

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Abel at 3/17/2009 10:30 AM
Gravatar
Worked like a charm for me on SQL05 with SP2. Thanks for the awesome post.

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Jawa Buddhavarapu at 5/26/2009 11:52 AM
Gravatar
Thanks! The script was just what I was looking for. Worked like a charm!

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by D Shah at 6/26/2009 9:45 AM
Gravatar
Thanks for the great script--helped me recover over 100 GB on our server in a matter of seconds!

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by sarath at 7/16/2009 4:23 AM
Gravatar
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

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Rashmi Ghosh at 7/29/2009 10:50 AM
Gravatar
Thanks its working.

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Jaime at 12/7/2009 1:55 PM
Gravatar
Thanks for the info Benjamin, it worked great.

# re: Truncate and Shrink an Overgrown SQL Server Log File

Left by Carolyn at 2/21/2010 8:58 PM
Gravatar
This worked great.. Got me out of a jam.

Thanks.

Your comment:



 (will not be displayed)


 
 
 
Please add 5 and 8 and type the answer here:
 

Live Comment Preview:

 
Archives
Post Categories
Team Foundation Server
Visual Studio Team System
Syndication:

RSS ATOM

«March»
SunMonTueWedThuFriSat
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910