October 23, 2009 at 1:20 pm
When I use
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
it performs a checkpoint, and the TLog should be truncated. but I do not see the TLog get into a smaller size, yet.
Without using DBCC SHRINKFILE, how/when will the TLog size become lesser.?
There were some big bulk inserts into the DB, and I should turned it into 'Bulk-logged' but I had not, and now I am wondering how to bring the DB back to normal size condition, without using Shrinkfile option.
Thanks
Dan
October 23, 2009 at 1:24 pm
All BACKUP LOG with truncate_only does is remove the commited transactions from the log. It doesn't do anything to make the size of the log data file smaller. You'll need to run the DBCC SHRINKFILE command if you want to reclaim the space for the operating system.
If this is a production system, you should immediately take a full or differential database backup because the truncate_only breaks the transaction log chain and leaves you vulnerable to a potential failure. If you need to truncate the log regularly, then you should probably put the database in Simple recovery.
October 23, 2009 at 1:47 pm
George Parker-378530 (10/23/2009)
All BACKUP LOG with truncate_only does is remove the commited transactions from the log. It doesn't do anything to make the size of the log data file smaller. You'll need to run the DBCC SHRINKFILE command if you want to reclaim the space for the operating system.If this is a production system, you should immediately take a full or differential database backup because the truncate_only breaks the transaction log chain and leaves you vulnerable to a potential failure. If you need to truncate the log regularly, then you should probably put the database in Simple recovery.
I wouldn't use the fact that I am required to truncate as the basis for putting it into Simple mode. As you mentioned - it breaks the transaction chain (essentially destroying your ability to do point in time restores). If you don't need PIT restores,THEN switch it to simple. Otherwise - find out why your log is getting so big and either increase your backup frequency or fix the code that is blowing out your transaqction log....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 23, 2009 at 2:45 pm
repent_kog_is_near (10/23/2009)
Without using DBCC SHRINKFILE, how/when will the TLog size become lesser.?
Never.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply