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'

Super fast Data Warehouse dimensions loading with SSIS and Change Tracking

The Challenge: Updating Type 1 dimensions as fast as possible. Source tables can be quite large and retrieving only changes and new data will reduce the time it takes to update your dimensions dramatically.

About this Blog and myself

Hello everyone,

My name is Luis Figueroa and I am an IT professional specialized on Microsoft SQL Server and BI. I am really passionate about architecting solutions that leverage the MS SQL Database Relational Engine and SQL server BI components to produce high performance solutions that deliver near real time data to businesses.

I am also an active member of the SQL Server community that enjoys learning and sharing knowledge  with others. I am the president for the Microsoft Business Intelligence User Group of South Florida and the Director of Sponsorship for the South Florida SQL Server User Group. I often speak at SQL Saturday events mostly in Florida and attend anywhere I can.

So as you would expect that is what this blog is going to be about. SQL Server / BI and the awesome SQL Server community. You can find me in LinkedIn and Twitter so if you have a passion for SQL Server or you are a Business Intelligence professional I would love to network with you. I am also always available to answer any questions whenever I can be of help.

I look forward to hearing from you,

Luis Figueroa