SQL Server 2008 - Truncating Transaction Log

How to shrink (re-size ) the transaction log in SQL Server 2008


Scenario :

I have a database with the following size

SELECT name,size from sys.database_files

Result
Test 173056
Test_Log 579072 -- 565 MB

Now I want to truncate Transaction log. In earlier version what we do is we truncate the log and shrink the file.

BACKUP LOG Test with Truncate_only

This statement throw an error with below message

/*------------------------
Backup log Test with Truncate_Only
------------------------*/
LHI-115\SQL2008(sa): Msg 155, Level 15, State 1, Line 1
'Truncate_Only' is not a recognized BACKUP option.

The errior is obevious, this command no more exists in SQL Server 2008. So the question is what is the alternative? As per books online “The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.” So this is the one command you should check before migrating to SQL Server 2008. If you have any script which have WITH TRUNCATE_ONLY option then you have to re-write the script.


How to shrink (re-size ) the transaction log in SQL Server 2008

As per books online , if you switch the Recovery Model to Simple inactive part of the transaction log should be removed. Let us see what happens


(a) select name,recovery_model_desc from sys.databases
Result
name recovery_model_desc

Test Full

(b) Alter database Test SET Recovery simple

Result
name recovery_model_desc

Test Simple


© select name,size from sys.database_files
Result
Test 173056
Test_Log 451352 -- 440 MB


This process reduced the the transaction log file size. But not to the size what i want. I have no option to set the required size as we had this option in SHRINKFile. Do we need shrink the file after switching the Recovery model? Not sure. I have tested the same database in 2005 and I was able to shrink the file to 1024 KB.

I have tried to shrink the TL after switching the Recovery model and the result is as follows :-

DBCC SHRINKFILE (N'test_log' , 1)

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
6 2 451352 128 451352 128


So.. its obevious that its not shrinking. There is no active transaction in TL, that also i checked.


Note : From my SQL Server 2005 and 2000 server i restored few database in 2008 and tried to Truncate TL. For some database this process worked. others do not. I need to look into it what is the problem with the database which could not shrink.

Summary:

In SQL Server 2008 , Transaction can be shrinked in two step.
(a) Change the Recovery Model to Simple
(c) Shrink the file using DBCC ShrinkFile