March 23, 2010 at 2:03 pm
hi guys i really hope you can help me, i have a ssis package which does a truncate of two tables and then does an insert, this package just keeps running forever... when i run sp_who2
i saw two similar spids creating blocks with this information:
blkby: -2SET OPTION ON suspended
running: exec [sys].sp_bcp_dbcmptlevel [Distributor] set fmtonly on select * from [dbo].[tblMainDistribMIA] set fmtonly off
after doing some research i saw that it could be something to do with the MSDTC settings?
is that true? how do i check that?
March 23, 2010 at 2:55 pm
Spid -2 is an orphaned distributed transaction. A distributed transaction that was cancelled improperly and SQL's left holding locks without knowing who they belong to.
To kill that, query sys.dm_tran_locks. There's a guid column in there, request_owner_guid. Take the value of that and pass it to KILL
eg:
KILL '0231B140-E604-47C1-8B51-08D88693351F'
That'll clear out the orphaned transaction and should unblock the other process. If there's more than one row returned by the query, check again after killing the first, you may need to kill more.
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
March 24, 2010 at 5:40 pm
thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply