October 13, 2005 at 9:05 am
Im getting the following error in the dts
Step 'DTSStep_DTSDataPumpTask_13' failed
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Transaction (Process ID 139) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Im pretty much sure that there is no other process that is working on that particular table(since this is a reporting database there might be select but not insert or update except this dts task). I could not understand why the dead lock is happening. any clues.
October 13, 2005 at 9:52 am
Does this happen everytime you run the package?
It is likely that the package is blocking itself, you may have some asyncronous events working on the same table. Use flow control to execute tasks syncronously.
Run the package, and then in query analyzer run sp_who2 and it will show you if blocking is occuring, and will tell you the process id's that are conflicting.
Then use
Dbcc Inputbuffer(spid) to see what is being executed. It both are from dts designer, then you know its your package causing the blocking.
October 13, 2005 at 9:57 am
That is the problem. The DTS does not fail each and every time. It fails once in a while. Any other method to catch that culprit causing dead lock
with smiles
santhosh
October 13, 2005 at 10:46 am
You can run a profile trace analyzing locks, Pick the events you want to monitor and wait for it to happen again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply