I recently observed that TFS transaction log is grown up to 200GB in SQL Server 2008 while actual DB data size is only around 20GB. This was because there was no truncation on the transaction log file.
TRUNCATE_ONLY is removed in SQL 2008. So now there are little more steps to follow to truncate transaction log properly in SQL Serveer 2008 / R2.
Here are steps to shrink transaction log for full recovery mode database in SQL 2008 / R2.
1. Backup Database
In SSMS Object Explorer, rightclick on
database => Tasks => [Back Up...] menu.
You can estimate DB backup size by using sp_spaceused before proceeding DB backup.
2. Backup Transaction Log twice
Bring up the same Back Up Database dialog as #1 above. Select Transaction Log in Backup Type.
You might need to backup transaction log twice to shrink log file correctly.
3. Shrink Transaction Log
In SSMS Object Explorer, rightclick on
database => Tasks => Shrink => [Files] menu.
4. Adjust Transaction Log size (Optionally)
In SSMS Object Explorer, rightclick on
database => [Properties] menu.
Change Initial Size of Log file.
Increasing transaction log might be useful if transaction log size is too small. If you know transaction log increment rate, you can adjust both initial size and Autogrowth rate.
