Post reply

dead lock error when execute DTS Package

  • April 10, 2006 at 12:25 am

    #68309

    Hi,

    i have created a DTS Package in which i call a stored procedure.

    when i execute the package i got an error in the middle as below.

    (consider the DTS package run 3 hours to complete the execution,

    the error im getting after 2 hrs of process, meaning i get the work done partially).

     

    "Transaction (Process ID 73) was deadlocked on lock resources

    with another process and has been chosen as the deadlock victim.

    Rerun the transaction. (Microsoft OLE DB Provider for SQL Server (0):"

    why is this error happened?.

    do i need to clear deadlocks for every time i execute DTS package?.

    then how to clear deadlocks?

     

    is there anything to do with SP_LOCKS or SP_WHO before i run DTS?.

  • April 11, 2006 at 4:45 am

    #631880

    Deadlocks usually mean that other processes outside of your package are trying to access the same resouces (tables) as your stored proc. If your sproc is running for three hours this contention may be happening quite often(and the other processes are losing out), and probably as expected at some point your package will lose out and get chosen as the victim.

    I would look at your stored proc and look closly at the locking stragetgy being employed and also see if you can optimise it to run for less time.

    If you look in the SQl Logs it will clearly state when and how often a deadlock situation is occurring.

    Jeet


    Thanks Jeet

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply