Post reply

blocked by spid -2

  • October 6, 2008 at 9:13 am

    #131578

    I have a job that is loading a table from oracle using SSIS. It is stuck with a -2 in the blocked by column in activity monitor. Anyone know what this means?

  • October 6, 2008 at 9:37 am

    #881168

    It's an orphaned distributed transaction. Basically something has cancelled a distributed transaction badly and SQL's left with locks it doesn't know what to do with.

    You can kill them safely, unfortunately it's not as easy as kill -2.

    Query the sys.dm_tran_locks view where the reques_session_id is -1 and look for the request_owner_guid

    You can pass that GUID as a parameter to kill to take care of the orphaned transactions

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 6, 2008 at 2:51 pm

    #881343

    Looks like it could be this issue:

    http://support.microsoft.com/kb/954669

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply