Spid -2 in sp_who2

  • 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

  • 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

  • Yes, it uses MSDTC for an IBM SQL JDBC driver. Is that creating the issue ?. How to solve it ?

  • 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

  • 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.

  • 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

  • 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.

  • 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