Post reply

deadlock-- immediate solution required

  • October 25, 2012 at 7:38 am

    #393457

    I got a job which runs daily having two steps

    1. it truncates the present database.

    2.It import the data from other resource into truncated database.

    Some times it goes good but once in a while it throws an error regarding deadlock. I want this job to be completed at any cost every time. How can I set deadlock priority to this? Can i do that?

    ERROR: Executed as user: . Transaction (Process ID 130) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.

  • October 25, 2012 at 7:40 am

    #1552696

    You can set its deadlock priority to high.

    Or, if you want help in fixing it, switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    p.s. Immediate solutions can be required from people who you pay. We're all volunteers who post in their spare time.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 25, 2012 at 8:25 am

    #1552718

    If you're truncating the entire database, how about setting it to restricted user prior to running the process? If you need to you can even rollback outstanding connections immediately (with all that implies of course).

    But, that assumes that the deadlock is from a second source. If you're getting deadlocks from your own data load process (due to parallelism or some part of your processing, hard to know), then this might not help.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • October 25, 2012 at 8:29 am

    #1552724

    stillconfused (10/25/2012)

    ...

    I want this job to be completed at any cost every time.

    ...

    You can try this idea:

    Make sure that no other session is using this database.

    Take the database to restricted user mode or kill other sessions which are having lock in this database.

Viewing 4 posts - 1 through 3 (of 3 total)

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