Identifying Sql INVOLVED in the Deadlock from the profiler

Question

Post reply

Identifying Sql INVOLVED in the Deadlock from the profiler

chinn

SSCrazy

Points: 2840
More actions
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!

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

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Sreejith Sreedharan

SSCertifiable

Points: 5403

September 6, 2006 at 11:55 am

#658826

Try This:

DBCC INPUTBUFFER(spid)

chinn SSCrazy Points: 2840 More actions · Answer 2

chinn

SSCrazy

Points: 2840

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,

Joshua T. Lewis SSC Enthusiast Points: 128 More actions · Answer 3

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.