Alternatives to KILL (SPID)

A few days ago there was a question in Twitter about options to KILL (SPID) with a long running transaction that was causing a lot of blocking in a mission-critical system. The person asking the question got some helpful tips on how to fix the problem, such as looking at the tables and the indexes and some tools were pointed out to him, like Adam Machanics Sp_WhoIsActive.

While these all were sound advice they didn’t really offer much of an opinion to using KILL, not that there are that many. In fact your only option to KILL is to wait it out.

If that doesn’t sound like a good option, consider this. If you have a transaction that’s been running for a fifteen or so minutes and you KILL it, it’s going to perform a ROLLBACK which will take equally long time. In some cases it might even take longer because the ROLLBACK process is single threaded where your aopplication might have used multiple threads for the original transaction.

If you’ve killed a long running process and it seems to take forever you can try and use KILL WITH STATUSONLY to get an estimate on how long it’ll take. If you have, for example, killed a process 71 with command:

KILL 71;

You can see and estimate on how long the rollback will take with command:

KILL 71 WITH STATUSONLY;

In short: I would advice caution before using KILL, but there are times when it simply cannot be avoided. On the other hand if you need to do it often, then I’d suggest what the other folks suggested in Twitter. Take a look at the query, the table and the indexes to reconcile the issue. If it’s something that cannot be fixed by optimizing, such as reporting workloads on huge tables it might be something that should be offloaded to reporting instance.

Author: Mika Sutinen

Hi, My name is Mika Sutinen and I'm a Senior Database Administrator for a company called Tieto. I've been working in IT-industry for two decades and I've spend most of my career working with healthcare information systems. I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s