SQLskills SQL101: Should you kill that long-running transaction?

As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

One of the problems you may have encountered is a transaction log file growing because of a long-running query.

What do you do?

The temptation is to kill the query, but is that the correct approach? Well, as always, the answer starts with ‘it depends’.

The first thing to consider is whether you’re using Accelerated Database Recovery in 2019+. If so, the transaction will roll back immediately, so go ahead.

The rest of this post assumes that you’re NOT using Accelerated Database Recovery.

A long-running query, no matter how much work it’s done (and hence how many log records it has generated) will prevent the log from clearing, as the log all the way back to the LOP_BEGIN_XACT log record of the long-running transaction is required, in case that transaction rolls back. And the log will not be able to clear until (at least) that long-running transaction has committed or finished rolling-back.

You can tell how many log records a transaction has generated using my script here, along with the total space taken up in the log by the transaction.

If the long-running query has generated hardly any log records, then killing it will mean that it rolls-back quickly and then hopefully the next log backup (in the full and bulk-logged recovery models) or checkpoint (in the simple recovery model) will allow the log to clear and stop its growth.

However, if the long-running query has generated a *lot* of log records, then it’s going to take a long time to roll back (as rolling back each log record means generating the ‘anti-operation’ for that log record, making the change, and generating *another* log record describing the change). That rollback itself won’t cause the log to grow any more, as a transaction always reserves free space in the log to allow it to roll back without requiring log growth (my script above also tells you that amount of space). However, as it will take a long time to roll back, other activity in the log from other transactions will likely cause the log to grow more until it’s finally able to clear.

So it may actually be better to allow a long-running transaction that’s generated a lot of log records to continue running until it completes. If the time to completion is going to be a lot less than the time to roll back, this could mean less overall extra log growth until you’re able to finally clear the log, and then potentially resize it back to normal, and continue running.

The trick is knowing what the query is doing and/or being able to figure out how close to completion it is. You could look at is the logical_reads column in the DMV sys.dm_exec_requests and correlate that with the number of pages in the index or table being scanned, or look at the number of log records generated in the script output and correlate that to the number of records you’d expect an UPDATE statement to perform.

Bottom line: don’t always knee-jerk and decide to cancel a problematic, long-running query without thinking about what the effect of that will be.