Denny's SQL Secrets

Dealing with Negative Session ID Denials in SQL Server

Microsoft SQL Server MVP Denny Cherry breaks down multiple ways on how to troubleshoot a type -2 blocking session ID error.

• By Denny Cherry
• 11/28/2013

When we look at sessions being blocked in SQL Server, the session most often causing the blocking is a Session ID (SPID). However, there are some occasions where the SPID doesn't make any sense. In these cases, you'll see a negative number as the blocking session.

A normal blocking report (see Figure 1) shows session 52 blocking session 54 due to a use transaction.

This type of blocking is quite normal and easy to troubleshoot. Things become much more complex when a type -2 blocking session is reported. The first reason this is harder to deal with is because SQL Server only supports positive numbers as Session IDs.

Before we can even begin to figure out how to solve this blocking issue, we need to understand what it means to have a Session ID of -2. This in itself a challenge because unless you're very familiar with searching on Google or Bing for a negative number, it can be tricky by itself as values with a minus sign in them are excluded from search results.

What Is -2?
A Session ID of -2 simply means it's an in-doubt distributed transaction. An in-doubt transaction is a distributed transaction that the Microsoft Distributed Transaction Coordinator (MS DTC) service isn't able to correctly identify to the SQL Server service. Maybe the MS DTC service crashed, or the MS DTC service has been restarted and there was a long-running transaction in process that needs to be rolled back by the SQL Server, but the MS DTC service is no longer aware of the transaction so the SQL Server needs to handle the rollback itself.

The first thing to remember about these transactions is that they are, or rather were, distributed transactions. The second thing to remember is the specific configuration setting of the SQL Server instance, specifically the "in-doubt xact resolution" setting of sp_configure. This setting tells SQL Server how to handle distributed transactions that have failed.

There are three sub-settings available for this setting. The default is 0. This will cause the transaction to fail if MS DTC isn't able to resolve the problem. The other settings 1 or 2 tell SQL Server to make assumptions and to act accordingly. When this setting is set to 1, it tells SQL Server to assume the transaction has succeeded and to commit the transaction. When the setting is 2, it tells SQL Server to assume the transaction has failed and it should be rolled back.

Assuming we've set the "in-doubt xact resolution" to 1 and we're seeing blocking, we don't want to do anything as SQL Server is attempting to commit the distributed transaction. If we've set the "in-doubt xact resolution" setting to 0 or to 2 and see blocking, we really don't want to do anything here either. The transactions are already being rolled back, so there's nothing else we need to do. The amount of time the sessions will take to roll back will depend on the amount of data that was modified by the transaction before it failed.

Attempting to kill session -2 using the "kill" command will simply result in an error message telling you that session -2 isn't a valid session. Restarting SQL Server will result in a long running of the crash recovery process the database goes through every time the database instance is restarted. There will be a large transaction in the transaction log that needs to be rolled forward or backward depending on the status of "in-doubt xact resolution." It depends on whether the transaction actually completed or not.

Just Wait and See What Happens?
Crazy as it sounds, my advice is to wait. In almost all cases, the best thing to do when you see transactions being blocked by a session with a Session ID of -2 is to simply wait it out.

If you're seeing this happen a lot, there are some other things you can try. The first is to figure out why the distributed transaction is failing. This could be anything including application problems, MSDTC problems, MSDTC crashes, network problems and so on.

If you can't identify the root cause, or can't fix it, you still have options. Most of these resolve around dealing with this as a normal long running query causing the blocking. You should identify missing indexes that could speed up the long running query. You could change the application code of the long running query, so the data modification is done in smaller batches instead of one large batch.

The third option would be to turn on Read Committed Snapshot Isolation (RCSI) so readers aren't blocked by updates anymore. The reader would be able to view the older versions of the data pages that have been copied into the tempdb database. You can enable RCSI at the database level by changing the "Allow Snapshot Isolation" database property (see Figure 2), or by using the ALTER DATABASE command (see Figure 3):

What Does All This Mean?
Sessions blocked by session -2 aren't any worse than sessions blocked by normal sessions. The database engine is doing what it's supposed to do by rolling the transaction forward or backward, depending on the setting of the "in-doubt xact resolution" setting.

The biggest difference here is the command being rolled back was run as a distributed transaction, instead of a more traditional local transaction. Due to the fact that this is a distributed transaction, the focus of the failure is a little different. You need to look outside the database engine at both the applications and the MS DTC service. These are just normal database operations and SQL Server ensuring the database within the database remains transactionally consistent.