July 12, 2008 at 4:05 pm
Hi Guys,
I am a bit confuse in Truncate and shrinking……when we say truncating the log file what does it mean?
As per my understanding shrining means - to compress the log file size which is empty in the log file e.g. if in 100 gig log file 40gig is used and 60 gig is unused then we can shrink the unused 60 gig. Now wts truncate here and wts the + and - points of truncate and shrinking???
Thanks,
DKG
July 12, 2008 at 5:08 pm
You have to understand a bit more about the log file to see the difference, but I'll give a short explanation. You can get more details in Books Online.
The log file is a circular file, so you allocate space, say 100MB, and that's used to write files. You might write 80MB of log records in there. Now you have 20MB free, 80MB log records. If you're in simple more, a checkpoint would mark all log records that were committed as "free" again, and once you wrote 30MB more of log records, 20MB would take the free space, the other 10MB would roll back and write back at the beginning of a file.
A log backup does the same thing if you are in full recovery mode.
A truncate does this without backup up the records, so if you want to restore to a point in time, you cannot as the log records are gone. Truncate should be rarely used, like if you're stuck and can't grow the log.
Shrinking the log file shrinks the physical size, though obviously it can't go smaller than the records that are in there. you might shrink if some event wrote a lot of records and grew the file to be larger than what is needed.
In general you want to do neither of these operations, nor do you want the log file growing. You want to set a size that handles the peak load of transactions between transaction log backups (or checkpoints).
July 12, 2008 at 5:54 pm
[font="Verdana"]Steve, First of all thanks a lot for this excellent explanation.
As per my understanding log file contains uncommitted transactions only, the transaction which is already committed has gone to datafile?
Now as per above you…
If you're in simple mode, a checkpoint would mark all log records that were committed as "free" again: doest it mean a checkpoint commits the uncommitted transactions which are in log file?
It means checkpoint commits the uncommitted transaction in log, therefore it goes to Datafile.
Now as per: A truncate does this without backup up the records::
Does it mean truncate does not use checkpoint to commit transaction, that’s why it got lost if we truncate log file in case we need to restore the log file?[/font]
July 12, 2008 at 8:57 pm
Truncate Transaction Log will truncate the the inactive transactions in your transaction log and shrink the transaction log file, but this will not reduce the TRANSACTION LOG File Size,
Whereas SHRINK Log will reduce the size of the transaction log file. To shrink a transaction log file to the specified size and to remove the unused pages, you can use the DBCC SHRINKFILE command. This will only shrink the inactive part inside the log file.
Maninder
www.dbanation.com
July 13, 2008 at 4:36 am
few more concerns after reading above 🙂
Truncate Transaction Log will truncate the inactive transactions in your transaction log and shrink the transaction log file, but this will not reduce the TRANSACTION LOG File Size,
Does it mean truncate the transaction log file - does truncation and shrinking as well? And it works for inactive portion only???
if it does shrinking as well then whey it doesn't reduce the log file as well.
July 13, 2008 at 6:44 am
DKG (7/12/2008)
[font="Verdana"]Steve, First of all thanks a lot for this excellent explanation.As per my understanding log file contains uncommitted transactions only, the transaction which is already committed has gone to datafile?
Now as per above you…
If you're in simple mode, a checkpoint would mark all log records that were committed as "free" again: doest it mean a checkpoint commits the uncommitted transactions which are in log file?
It means checkpoint commits the uncommitted transaction in log, therefore it goes to Datafile.
Now as per: A truncate does this without backup up the records::
Does it mean truncate does not use checkpoint to commit transaction, that’s why it got lost if we truncate log file in case we need to restore the log file?[/font]
truncate does not remove uncommitted transactions, only committed\inactive transactions. Truncate does not shrink the logfile it only removes objects inside the file. You must read BOL to get a further understanding of this. Truncating a logfile will also break your backup chain and unless you act accordingly after a truncate you could find yourself losing data if you need to restore
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 14, 2008 at 4:04 am
Hi,
check this article about transaction log:
http://www.sqlservercentral.com/articles/Design+and+Theory/63350/
Cheers:)
July 14, 2008 at 4:20 am
DKG (7/12/2008)
As per my understanding log file contains uncommitted transactions only, the transaction which is already committed has gone to datafile?
The transaction log contains records of uncommitted and committed transactions.
doest it mean a checkpoint commits the uncommitted transactions which are in log file?
A checkpoint doesn't commit anything. The only thng that commits a transaction is the COMMIT TRANSACTION statement
What a checkpoint does is write dirty data pages to disk. Once the dirty pages are on disk, then there's no danger of losing the changes the transaction made should the server fail. Once all of the pages modified by a trasnaction are on disk, the log entries for that transaction are considered inactive. That is, they are not needed for the recovery process should the service restart
that’s why it got lost if we truncate log file in case we need to restore the log file?
Restoring transaction log backups requires an unbroken log chain. Truncateing a log discards some log entries and hence breaks the log chain.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply