May 17, 2011 at 11:25 am
When I run sp_who2 I see a session who is on the top of the deadlock chain is being blocked by a spid -2 .Does any one have an idea what spid -2 means ?
Regards
YeePee
May 17, 2011 at 11:32 am
This appears to be an orphaned distributed transaction SPID. Do you have MSDTC running transactions from an application to this database?
Check this resource on how to kill that spid.
http://www.sqlserverclub.com/articles/how-to-deal-with-negative-spids-in-sql-server.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 17, 2011 at 11:50 am
Yes, it uses MSDTC for an IBM SQL JDBC driver. Is that creating the issue ?. How to solve it ?
May 17, 2011 at 12:05 pm
msdtc is at the root of the issue. Optimize the schema and queries. But if msdtc loses connectivity from the app to the database during execution you have other issues at hand. You may want to talk to your network guys or the app team.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 17, 2011 at 12:35 pm
Thanks Jason. I will check with App Team. This is not a production environment , we are running few tests using the driver. I supsect it is an issue with the IBM JDBC driver.
May 17, 2011 at 12:45 pm
Good luck
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2011 at 2:09 am
I have seen this a number of times in relation to MSDTC and BizTalk, with one of the instances causing quite a significant problem in the live environment.
To get the UOW ID we run the following statement
select req_transactionUOW
from master..syslockinfo
where req_spid = -2
Then you can usually KILL the UOW by doing KILL 'UOWID'
The one major issue we had is that the UOW didnt want to terminate, so we restarted the services and the DB was put into recovery and wouldnt come back online due to the transaction.
To get this resolved we had to go into Component Services and manually force a termination of the UOW.
Will always remember to use Component Services first before rebooting SQL.
May 18, 2011 at 8:46 am
That is good info to have too. Thanks Anthony.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply