I was troubleshooting a foreign key violation error in a package that populates a fact table and ran into an interesting situation. When running the package from Visual Studio it ran up to the point of the final insert in the fact table at which point it hung.
Off course I proceeded to troubleshoot this and found that the final insert was been blocked by SPID -2. After running all sort of queries using different management views I had no info as to what was blocking the insert. I turned to google and finally I read an entry in a forum that described this SPID -2 as an orphan transaction and pointed out that the fix was restarting MSDTC. Before doing that I wanted to at least confirm this so I started querying again using the transaction related management views but could not find this orphan transaction. At that point decided to restart MSDTC and ran the package again.. surprise! The package ran all the way. I had tried several times before doing this with the same results (process blocked by SPID -2)
After further research I found that this SPID is visible by querying sys.syslockinfo (I guess I missed this one on my initial query attempts) from there you can obtain the req_transaction_uow then you can kill this unit of work by using the kill command followed by the req_transaction_uow. No need to restart MSDTC
example:
select req_spid, req_transactionuow from sys.syslockinfo
-2 D5499C66-E398-45CA-BF7E-DC9C194B48CF
57 00000000-0000-0000-0000-000000000000
51 00000000-0000-0000-0000-000000000000
58 00000000-0000-0000-0000-000000000000
59 00000000-0000-0000-0000-000000000000
kill 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'
