Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    13

    Exclamation Unanswered: SQL Server deadlock on resources??

    We've got a 3rd party application that periodically runs SQL commands throughout the day. We've been getting issues with this application showing a sql error:

    Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    In checking the processes on SQL Server, there were a lot, and process ID 71 was actually hitting a completely different database.

    Is there a way to streamline how SQL Server handles processes, and what's the limit at any given time?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I think you need to do a bit more research on deadlocks in SQL Server. When a SPID is determined to be the victim of a deadlock, that SPID is immediately killed. What you saw in a different database was likely some new connection that jumped into the gap.

  3. #3
    Join Date
    Nov 2005
    Posts
    122
    Deadlocks are usually the result of bad programming practise.

    If I remember correctly you can trace deadlock events in SQL Server Profiler.
    Use it to track down the sql statements that are causing the deadlock. Most likely someone will have to re-program something.

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Yup - I'd start with SQL Profiler - first try to configure the filter so it only looks at the third party app.

    It's possible the app is opening numerous SPIDS / Connections to do it's job - this could cause one SPID to be working on the same data as a seperate SPID from the same application.

    can you get the 3rd party app to cause an error whenever you want - if so it won't be as difficult as looking at everything it does via Profiler and trying to work out where a deadlock would likely get created.

    Good Luck

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Nov 2005
    Posts
    122
    I've checked Profiler now, and what you should do is to start a trace with the Lockseadlock graph, Lock: events. When the deadlock occurs it will tell you which objects are involved. You could also add the Lockseadlock event to see which spid (and application) that was chosed as the victim.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •