April 3, 2006 at 10:21 pm
Hi I want to clear the Transaction log. I tried "DBCC SHIRINKFILE(logical log file name, TRUNCATEONLY)" But its not working. It does not truncate the log file. some times it works and some times it does not. can you please help me.
April 3, 2006 at 11:32 pm
April 4, 2006 at 8:43 am
There's also a NO_LOG option. Look them up in BOL to see which might be more appropriate.
April 4, 2006 at 9:32 am
Best combination to use is a BACKUP LOG ... command followed by a DBCC SHRINKFILE command.
BACKUP LOG [ with truncate_only ]
DBCC SHRINKFILE ()
If the log records are being particularly stubborn, you may have to do this twice.
Bear in mind - if there's a long-running open transaction still executing then you won't be able to shrink the log beyond this point at all. The same is true if your database is marked for replication publication and some transactions have not been replicated yet.
April 5, 2006 at 4:25 am
Try this sql.....
use master
BACKUP LOG "your database" WITH TRUNCATE_ONLY
use "your database"
dbcc shrinkfile ("your database log file name", 0)
April 5, 2006 at 7:00 am
The issue here is the 'active' and 'inactive' parts of the transaction log. The active portion are those transactions that have not been committed to the database. You cannot truncate the active portion of the log. This can cause a problem. Here are two examples: The A stands for active and the I for inactive.
1st Log:
AAAAAAAAAAAAAIIIIIIIIIIIIIIIIII
A truncate will remove all the I's from the log and a shrinkfile will eliminate the free space.
2nd Log:
IIIIIIIIAAAAAAAAAAAAAAIIII
A truncate will only remove the I's at the end of the log and a shrinkfile will only free up the space used by the I's that got removed.
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply