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
select req_spid, req_transactionuow from sys.syslockinfo
In SQL 2005/8, its probably better to use the DMVs to find the unit of work causing the locking:ReplyDelete
SELECT request_owner_guid as [UoW ID] FROM sys.dm_tran_locks WHERE request_session_id = -2
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.ReplyDelete
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.