What Happens in a Transaction
When a transaction is being committed, each step is written into the transaction log. Each value that is being inserted, updated, or deleted is recorded so that if there is a failure before the log is written to the data file, the appropriate rollback or roll forward can take place. The transaction may take and release locks on tables or indexes.
What Happens in a Rollback
When we issue a KILL, every step of the transaction that has been written to the transaction log must be undone. The object the transaction was changing must be put back to the state it was in prior to the change. Was a record inserted? It needs to be deleted. Was a record updated? The previous value must be restored. Was a record deleted? It needs to be added back in. You can use fn_dblog to see this in action, too.
If SQL Server is simply undoing what was once done, why does it sometimes take longer?
If the transaction had to take a lock on a row or table to perform the operation, it will have to take that lock again – and other processes might be using that object now. Also, consider that a rollback is mostly single-threaded. If the transaction ran using four cores initially, and the rollback is now only using one, it will take more time.
Imagine this: you’ve decided to climb a tower with 10,000 stairs. You get to stair 9,999 and decide you don’t want to complete the climb. You can’t wish yourself to the bottom step – you have to go back down. But now, you’re tired – and have to do this single-threaded. Imagine having to hop down the stairs backwards, on one foot.
TRACKING PROGRESS
You are able to track the progress of a rollback. After you issue a KILL command, run KILL [Session ID] WITH STATUSONLY. This will show you approximately how much time is remaining in the rollback – but other processes in the database could affect that. Consider the information here a guideline, not a hard-and-fast time.
Kill Example:
EXEC sp_who2
-- 54 Stuck in ROLLBACK and blocking other processes.
KILL 54
KILL 54 with statusonly
--SPID 54: transaction rollback in progress. Estimated rollback completion: 93%. Estimated time remaining: 282 seconds.Sources:
https://www.brentozar.com/archive/2014/03/happens-issue-kill/
Comments