I probably had the most fun all week when a query I was running came up blocked. Sounds strange right? Well the blocking_session_id was a negative 2 (-2)! I’ve never seen anything like it before. Once I had resolved my problem (see below). I started doing some research on negative session_ids in general. To start with there are the BOL entries for sys.dm_exec_requests

blocking_session_id

ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

-2 = The blocking resource is owned by an orphaned distributed transaction.

-3 = The blocking resource is owned by a deferred recovery transaction.

-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.

And sys.dm_tran_locks

request_session_id

Session ID that currently owns this request. The owning session ID can change for distributed and bound transactions. A value of -2 indicates that the request belongs to an orphaned distributed transaction. A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

Note that between the two of them we see possible negative values of -2, -3 and -4.

I’m going to go in reverse order since that matches how difficult it was to find information on each (-4 was by far the hardest).

-4 : Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.

I found a couple of links on this. Here and Here. Both links had some interesting information and in both cases it appeared to be an issue with tempdb. The second in particular had a block of information from Microsoft.

This is directly from Microsoft:

Troubleshooting contention in DDL operations

Evaluate your application and query plans and see if you can minimize the creation of temporary tables. To do this, monitor the perfmon counters Temp Tables Creation Rate and Temp Tables For Destruction. You can also run SQL Profiler to correlate the values of these counters with the currently running queries. This will help you identify the queries that are causing the contention in system catalog. This might occur, for example, if a temporary object is being created inside a loop or a stored procedure.

Verify if temp objects (temp tables and variables) are being cached. SQL2005 caches Temp objects only when the following conditions are satisfied:

• Named constraints are not created.
• Data Definition Language (DDL) statements that affect the table are not run after the temp table has been created, such as the CREATE INDEX or CREATE STATISTICS statements.
• Temp object is not created by using dynamic SQL, such as: sp_executesql N’create table #t(a int)’.
• Temp object is created inside another object, such as a stored procedure, trigger, and user-defined function; or is the return table of a user-defined, table-valued function.

-3 : The blocking resource is owned by a deferred recovery transaction.

Really the only good information I could find was a post by Paul Randal and a BOL entry. Of course given the sources I consider both of these pretty definitive. Both state that this is a problem with an uncommitted transaction that was trying to be rolled back when a database is being brought online. From what I understood of Paul’s post the only way to recover from this was to restore the database from backup.

-2 : The blocking resource is owned by an orphaned distributed transaction.

This is the particular problem I ran into. I did some research and didn’t come up with anything quickly so I posted on dba.stackexchange.com and between Martin Smith and Thomas Stringer they pointed me in the right direction. I found this link on www.sqlservercentral.com and it had the solution I ended up using. (Getting the req_transactionUOW from syslockinfo and using that value with KILL.) The problem here is again a transaction that is trying to be rolled back. In this case it’s a distributed transaction that got lost by MSDTC (Microsoft Distributed Transaction Coordinator). A distributed transaction is one that is taking place in multiple databases, frequently on multiple servers. If a problem occurs and the correct state of the transaction can’t be determined you run into this problem.

There you go. Negative session_ids. Hope you had as much fun with them as I did.

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL, Transactions Tagged: code language, DMV, language sql, microsoft sql server, problem resolution, sql statements, system functions, T-SQL, transactions