November 9, 2016 at 9:16 am
Hi, I have a question I'm hoping a Transaction / Tran Log expert amongst us might be able to help with. It's a behaviour I've noticed repeatedly but I can't seem to find an answer as to why it occurs, and it's suddenly become highly relevant.
So, for illustration, let's suppose I have a database using Full recovery model and I arbitrarily truncate & shrink my transaction log (incredibly bad practice, yes, bear with me), then full-backup to restart the tran log "chain" - but my log is for all intents, empty:
DBCC SQLPERF(logspace)
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
ab 0.9921875 35.03937 0
Then I perform some big update in a transaction:
BEGIN tran
UPDATE dbo.SillyTable SET SillyCol = 123
DBCC SQLPERF(logspace)
(100000 row(s) affected)
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
ab 46.49219 94.87481 0
So, most of the tran log is full as I would expect (assuming moderate file growth settings, etc.)
Then, I commit:
COMMIT
DBCC SQLPERF(logspace)
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
ab 46.49219 25.82444 0
and suddenly only 25% of the log is used.
What's happening here? Why is nearly 75% of the log being used just because the data isn't committed yet? And what "is" that 75% that it is important to be retained pre-commit but suddenly doesn't matter anymore once it's committed? Surely whatever it is would still be needed in order to perform a restore from the transaction log backups?
Thanks for your thoughts and considerations, in advance 🙂
November 9, 2016 at 9:59 am
Log Reservation.
A transaction MUST always be able to roll back, even if the transaction log is full and can't grow. Since a rollback involves creating and then logging compensating operations, that means that there must be space reserved in the log while a transaction is active, in case it needs to roll back. Once the transaction commits, this is no longer needed and can be released, leading to the increase in free space that you see.
It's briefly discussed in http://www.sqlservercentral.com/articles/Stairway+Series/94579/
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
November 10, 2016 at 1:50 am
GilaMonster (11/9/2016)
Log Reservation.
Thanks Gail. I had a feeling in my bones that it would be you that answered this one :-). Much appreciated as usual.
The linked article reads familiar, I think I read it near when it was first published in the context of understanding VLFs too.
I'm still not entirely sure why the system seems (generally and only casually observed) to hold around 70-75% of the space it's used to perform the transaction, I would have thought 50-55% would be sufficient. But this is only academic interest.
Cheers.
November 10, 2016 at 2:04 am
Since the consequence of making too low a log reservation can be very severe (DB goes SUSPECT if a rollback can't complete), the algorithm that estimates how much space will be needed to reserve is a very cautious one and will often estimate more than would actually be needed.
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
November 10, 2016 at 4:34 am
That makes sense, thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply