It’s one of those simple things that you’re sure you should know, but are pleasantly surprised when you discover it.
The scenario was that a vendor-supplied batched audit history deletion job was running, and the transaction log was growing… and growing… The batching appears to work well from Management Studio, but not so well from the application – my guess is that the application is creating an outer transaction, preventing the inner transactions in the batch process from actually committing.
After an hour of running, the job failed, running out of log space with a 60 GB transaction log (no major issue – it was a test server), and started to rollback.
How long will this rollback take? In theory, it should be less than the hour we’d already spent – we don’t have to grow the log, and we don’t have to run through the batching process repeatedly to determine which rows to delete.
The solution? The KILL command can retrieve the status of the rollback. Normally you’d use this to see how long a killed process will take to rollback, but it will work fine for out-of-space rollbacks too.
KILL 101 WITH STATUSONLY;
With the result being:
spid 101: Transaction rollback in progress. Estimated rollback completion: 65% Estimated time left: 933 seconds.
Note that running the command repeatedly won’t make the rollback happen any faster, but it’s not likely to slow it down any either.