When cancelling a transaction, be it e. g. via failure or manually when it runs too long, what is the effect on the transaction log and its size?
Do all the entries generated during and related to the transaction get deleted again (in both DB and LOG) or does the undo result in the same number of entries in the log, just undoing what was done before, growing the LOG file further?
Since deleting might be difficult in case of overlapping operations, I tend towards the latter, still I wonder and could not find out.
Best Answer
While the transaction is open and working toward its goal, it'll reserve some log space in case it needs to rollback. Generally then, a rollback shouldn't change the size of the log as the space has already been reserved, though it is just an algorithm so it could reserve too much or too little and you won't know until it's too late. There is no way to change this behavior, currently.
There have been some announcements with SQL Server 2019 that will change the way this works if you turn on new features, FYI.
It doesn't have to be one-to-one as compensation records are created to undo the effects of the transaction, thus cancelling out the changes. Nothing is physically deleted, the opposite (compensation) operation is performed for each item in the transaction. Which compensation records and how many, depends, but the types should stay the same (the anti-type of the original).