Sunday, October 24, 2010

Package execution waiting due to SPID -2 blocking insert

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'





Posted by

2 comments:

  1. AnonymousOctober 24, 2010 at 1:23 PM

    In SQL 2005/8, its probably better to use the DMVs to find the unit of work causing the locking:

    SELECT request_owner_guid as [UoW ID] FROM sys.dm_tran_locks WHERE request_session_id = -2

    ReplyDelete
  2. Luis E. FigueroaOctober 24, 2010 at 4:03 PM

    Thanks for the feedback. In fact sys.syslockinfo is one of the system tables that will be depreciated and which functionality is available in sys.dm_tran_locks as you pointed out.

    Here is a link with the corresponding mapping between the old system tables and system Views implemented in SQL 2005 and to be used moving forward.

    http://msdn.microsoft.com/en-us/library/ms187997.aspx

    ReplyDelete

Subscribe to: Post Comments (Atom)