SQL Server Performance

Deadlocks - How to Get Names of the Stored Procedures Involved?

Discussion in 'SQL Server 2005 General DBA Questions' started by jbates99, Feb 3, 2011.

  1. jbates99 Member

    hi experts,
    My 2005 database is experiencing lots of transaction deadlocks.

    I want to run a Profiler Trace that will collect the Database Name, names of the stored procedures. And if possible the table name that was deadlocked.
    But the most important things are the SP names.
    Which events do I need to collect in my profiler trace?

    Thanks, John
    jbates99, Feb 3, 2011
    #2

  2. FrankKalis Moderator

    FrankKalis, Feb 3, 2011
    #3

  3. RamJaddu Member

    RamJaddu, Feb 4, 2011
    #4

  4. satya Moderator

    Best bet is to refer SQL Server error log which can get more information too.
    satya, Feb 7, 2011
    #5

  5. jbates99 Member

    Satya, I have far too many deadlocks to have to walk thru the logs manually. That's why I configured a Profiler Trace to write the information into a table that I can generate a report from.
    And yes the proc names are embedded in the huge deadlock graph's Text column but that also requires a manual review.
    Thanks to everyone who replied.
    John
    jbates99, Feb 7, 2011
    #6

  6. satya Moderator

    John
    I agree it is not quicker way to find out, but in case the server is already hammered with deadlocks the performance will be dented when using PROFILER or any other monitoring tools to get the information.
    Anyway you need to go through the TSQL or stored procedure from that Text column and reduce the deadlocks.
    satya, Feb 8, 2011
    #7

  7. yuanyelss New Member

    If the server has been finalized and will weaken the performance of deadlock using Profiler or any other monitoring tools to obtain information.
    yuanyelss, Feb 18, 2011
    #8

  8. satya Moderator

    In any case running a server side trace and capture DEADLOCK graph is best way.
    satya, Mar 7, 2011
    #9

Share This Page