June 17, 2014 at 7:34 am
So we had a bunch of deletes wrapped in a single transaction and it ran the drive out of space. How does it handle the rollback?
Basically someone asked can we pick up where it left off? But the records are back in the tables in which I assume because of rollback.
June 17, 2014 at 7:40 am
I think I found the answer on http://msdn.microsoft.com/en-us/library/ms180892(v=sql.100).aspx
Rollback operations are also logged. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.
June 17, 2014 at 7:43 am
Since transaction are atomic, you cannot resume them somewhere in the middle. Either it finished, or it rolled back and "never happened".
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 8:29 am
So if you don't have enough log space to do the delete in a single step, break it up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 17, 2014 at 12:17 pm
ggoble (6/17/2014)
Rollback operations are also logged. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered.
Exactly. It's called Log Reservation and it's the reason why you can sometimes see the log used % drop after a transaction commits while in full recovery (and no log backiup has run). The algorithm for estimating space is generous, it'll usually reserve more than it needs, for good reason. If the rollback fails due to insufficient log reservation and the log is full, the DB has to be marked suspect.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply