Post reply

Identifying Sql INVOLVED in the Deadlock from the profiler

  • September 6, 2006 at 11:32 am

    #367310

    Hello,

    We are having a deadlock issue so i decided to set up the trace and it captured deadlocks i can see them like this

    EventClass                    TextData                                 StartTime                             SPID      EndTime

                  Lockeadlock Chain      Deadlock Chain SPID = 222    2006-09-06 01:04:54.587    4         Null

    Lock   Lockeadlock                                                            2006-09-06 01:04:53.570        265    2006-09-06 1:04:54.587

    Now if i am correct top line is victim which never finished and the bottom is finished.Now how do i find the texdata for both the victim and the one that finished.

    Thanks in Advance!

     

  • September 6, 2006 at 11:55 am

    #658826

    Try This:

    DBCC INPUTBUFFER(spid)

  • September 6, 2006 at 12:14 pm

    #658841

    Hello,

    I don't think that works because i ran the trace last night.I am thinking profiler might have captured those there should be some where in the profiler i am just having hard time relating the spids...

    Thanks,

     

  • September 6, 2006 at 12:58 pm

    #658861

    Typically when I'm identifying deadlocks I try to stay away from a trace because of the amount of data that needs to be sifted through, and because the events set to be logged in the trace can vary based on the the nature of the deadlock.

    My tried and true deadlock solution has always been:

    DECLARE @SPID int

    DECLARE @CmdStr nvarchar(255)

    CREATE TABLE #Who2(

    SPID int,

    Status varchar(255),

    Login varchar(255),

    HostName varchar(255),

    BlkBy varchar(255),

    DBName varchar(255),

    Command varchar(255),

    CPUTime int,

    DiskIO int,

    LastBatch varchar(255),

    ProgramName varchar(255),

    SPID2 int)

    INSERT #Who2 EXEC sp_who2

    SELECT @SPID = spid FROM #Who2 WHERE spid IN (SELECT BlkBy FROM #Who2 WHERE BlkBy NOT LIKE '%.%')

    AND BlkBy like '%.%'

    SELECT * FROM #Who2 where spid = @SPID

    IF @SPID is not null

    BEGIN

    DBCC INPUTBUFFER(@SPID)

    END

    ELSE

    PRINT 'No Deadlock currently present'

    If this is executed while a deadlock if occuring, it will run an sp_who2, logically determine the head of the deadlock, and if the value of @SPID is not null, the DBCC INPUTBUFFER command is run specifically against the blocking process, giving you the raw SQL command it was attempting to run. I find this very effective, because it will also capture commands coming from code, not just native SQL queries (I.e. SProcs, Triggers, views).

    Once you've captured all the pertinent data about what was causing the deadlock, manually use the KILL(spid) to bring the system back up before the 20 minutes timeout occurs.

  • September 7, 2006 at 11:03 am

    #659163

    Oh, and ignore the @CmdStr variable....that was for a future developement I was working on.

  • September 7, 2006 at 6:42 pm

    #659236

    Thanks Joshua T. That's a very good procedure. I'll experiment with that at the office.

    "If this is executed while a deadlock if occuring.."

    But I never know about a deadlock until well AFTER it has occurred (via an email alert).

    John

     

     

  • September 8, 2006 at 4:45 am

    #659307

    I've enabled the following traceflags so the deadlock situation appears in the logs

    dbcc traceon (1204, 3605, -1)

    go

    dbcc tracestatus(-1)

    go

    http://support.microsoft.com/kb/832524/

Viewing 7 posts - 1 through 6 (of 6 total)

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