How to detect and resolve deadlock situations in SQL Server

Posted on 2011-10-17
Last Modified: 2012-05-12

I have couple of question regarding SQL Server:

1. How can I detect deadlock sitation in SQL Server and can I resolve them automatically?

2. We run SQL jobs on a definite schedule. Is there a way I can create a report of selected SQL jobs, time taken by them to complete execution, failure if any, and number of records affected.

Thanks !
Question by:pratz09
    LVL 22

    Accepted Solution

    Question 1:
    If it is a true deadlock, SQL Server will detect it and automatically resolve it by killing all but one of the processes involved in the deadlock.  The trick is, SQL Server will decide who lives and who dies. ;-)

    Question 2:
    You might want to look into Pragmatic Works' bi-Xpress tool.   It can set up the audit logging for all tasks in SSIS packages in a way that will let you report on the information that you indicate you want.

    Author Comment

    Thanks a lot !

    May be something is wrong with how our systems are set up, but SQL Server takes long time to finally detect that there is a deadlock. Can I programmatic-ally find out if there is a deadlock and instead of killing the process, suspends one and resume it later ?

    Thats a great tool. WIll be very useful. How Can I report same things for Stored Procedures ? I looked at SQL Profiler, its close but couldn't find the exact information I need.

    Thanks again !
    LVL 22

    Assisted Solution

    Well, you might also want to check out Confio's Ignite tool.  It monitors database (without putting nearly the load of SQL Profiler) and provides all manner of interesting information about the top 50 worst performing queries (whether within an SP or not).

    As for SQL Server taking a while to recognize the deadlock, that is true . . . SQL Server tries to let the queries sort things out for themselves before intervening and killing one (or more) of them.  However, by the time you would be able to identify that there was a deadlock, it would probably be too late to select a query to suspend, even if you could suspend one selectively.

    Author Closing Comment

    I agree. Thank you !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now