(session ID -2 is not valid) Dealing with Negative Session ids in Sql Server

-

 

SQL server being a complex mechanism of handling processes throughout their life cycle often gives out interesting anomalies,

One such was encountered today on one of my production servers, there was a huge blocking on chain of processes and at the root of this chain was a session with the weirdest session id 

The session id literally was " -2 "  ,  it is hard to make sense out of it as SQL server always works with positive spids. Research on this is itself a tedious job. After the R&D phase it was somewhat easier to diagnose the problem.

The root cause refers to a phase of distributed transaction, A distributed transaction is referred when a process is extended over more than one servers i.e Process A of server 1 needs data to be manipulated on Server 2, so SQL server uses MSDTC (linked server) service to carry this process from one server to another.

Going back to our issue, now

According to msdn and other online resources, the session id -2 means that the MSDTC service no longer knows what to do for that process\transaction, it is " in-doubt ", it can no longer refer it to sql server. Often termed as Orphaned transactions , these have unknown state.

Possible causes can be Network issue, MSDTC service crashing or sudden restart of the same service while there was a heavy dml transaction in execution.

Even a remote query timeout in this chain of MSDTC transaction can cause the service to fail the transaction, The Failed transactions rollback at a speed depending on the amount of changes it made. 

Killing such process is the solution, Yes you might opt to hard bounce the server in order to get rid of any pending transactions,  but hard bounce with a scenario where possibly a heavy transaction is in rollback or commit will only cause difficulties in recovering the database once it comes online. 

Another is to check in "Transaction Statistics" of  "Local DTC" in "Component Services" , it will actually show the in-doubt transactions and then give a option to resolve (commit,abort,forget), if it doesn't then go on to kill 

A session with negative spid cannot be killed using our common kill command, if tried it will give you an error such as 

Msg 6101, Level 16, State 1, Line 1

Process ID -2 is not a valid process ID. Choose a number between 1 and 2048


need to follow the below steps to kill the process 

1. select req_transactionUOW from master..syslockinfo where req_spid = -2

2. KILL <req_transactionUOW >



Comments

Post a Comment

Popular posts from this blog

SQL Server’s STATISTICS IO

-
Image
Usually the goal of performance tuning is to make your query run faster.  One of the easiest ways to get a faster query is to reduce the volume of data a query is processing.  STATISTICS IO makes it easy to see how much data SQL Server is actually processing. How it is going to help?  The data it shows acts as a measuring for your performance tuning.  It provides a good way of knowing the number of io Sql server has gone to fetch the data  It is session specific only How to Use? -- For current session only SET STATISTICS IO ON; GO How to read the output?  Logical reads: The number of pages SQL Server had to read from the memory in order to process and return the results of your query.  Query performs slower if number of pages read is high.   Physical reads:  this read happens when the page requested is not present in the cache and needs to be fetched from files on the disk, this operation causes a IO wait.   Worktables/Workfiles:  sql server cre...
Read more

ORA says NO DATA FOUND

-
Image
Linked servers always have a surprise error hidden under its selvee, Managing these issue magnets is a huge task for a DBA , recently i was stumbled upon a same suprise invloving a oracle linked server. The linked server "oracle_db" is present on my prod db server essentially for select and insert purposes, Developers tested the linked server with couple of select querries and all was fine until the deployed a procedure on server and executing the same gave a alien error OLE DB provider "OraOLEDB.Oracle" for linked server "oracle_db" returned message "ORA-01403: no data found" inspecting the code it was seen that the table from linkedserver was directly involved in a " inner join" and other joins ,re running the same satement produced the given error . The code snippet was pinned, diagnosis and resolution was underway After a thorogh R&D it was found that either taking the whole oracle side output into a #Temp table and then using t...
Read more