SQL Server Performance

Notification of deadlock?

Discussion in 'Performance Tuning for DBAs' started by Chappy, Feb 6, 2003.

  1. Chappy New Member

    Hi all. Just had a nice case of deadlock, conflict between our inhouse app and a clients app who access our database over the WAN.
    In Enterprise Manager it is able to infer processes which are deadlocked by saying 'blocked by PID 32 etc'. What I want to know is if there is any way I can recieve notifications when deadlock occurs ? Or I wonder if any table holds info about locks so I could possibly have a job scheduled to examine this periodically and fire me an email when locks appear to depend on each other..

    Also, in EM, the locks and process info trees never seem to refresh until Ive closed and reopened EM. Is this true, and if so is there any way to refresh their lists.

  2. barozai New Member

    You can trace it by using Profiler (SQL Trace).
    for further info use BOL.

  3. HarryArchibald New Member

    There is a rapid blocker script from MS at
    support.microsoft.com/servicedesks/ webcasts/wc011502/WC011502.ppt. I found this gave back info but it was hard to understand.
    I then foudn more info at Q283696, Q283725 and Q283784. I use this everyday to monitor performance and blocking. Rather than notifying about deadlocks, the info allows me to understand exactly what happened and then make chanegs to prevent the problem.

    In addition, dbcc traceon(3604) and dbcc traceon(1204) will send deadlock info to the errorlog. I found 1204 more useful than 1205.

    Good luck.

  4. bradmcgehee New Member

  5. sqljunkie New Member

    Check out the sysprocesses table in the master database. There are spid, blocked and waittime and lastwaittype columns that will give you the information you're looking for.
    You could write a query to check if a blocked spid is blocked by another spid...
  6. Chappy New Member

    Thanks, this is what I was looking for

Share This Page