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
Saturday, February 23, 2008
Subscribe to:
Post Comments (Atom)
5 comments:
Try a log backup, TWICE, then shrink. I read somewhere there is a bug.
Your file_id is incorrect, its is probably 2, not 1.
And after the database is shrinked, do I have to take back the Recovery Model to Full???
@Carolina.
Of course you should change your Recovery model to FULL. Otherwise, point intime restore will not be available for the db.
I think you can also shrink the log by doing a transaction log backup. (No need to change recovery mode.)
You then have the choice of either keeping the TLOG backup in case you want to restore it for point in time restores beyond your last full backup, or to recover transactions since your last full backup in the event of losing your main mdf file due to a problem.
For this last reason I think an hourly backup schedule is recommended for the TLOG.
Post a Comment