How to find out cause of deadlock?

Question

Post reply

How to find out cause of deadlock?

AAKR

SSCommitted

Points: 1586
More actions
June 11, 2013 at 11:21 am

#294461

Hi All,
2days ago we have a dead lock.
how to find out, for what cause the dead lock got occured?

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

AAKR (6/11/2013)
Hi All,
2days ago we have a dead lock.
how to find out, for what cause the dead lock got occured?

How do you know you had a deadlock 2 days ago?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WolfgangE SSCertifiable Points: 6328 More actions · Answer 2

You seem to know THAT and also WHEN you had a deadlock.

Active the trace flag 1222 on the server. If a deadlock occurs you can find a lot of useful information about the deadlock. If you know the time it occurs just have a look the sql server log at this time and get your information.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

That's the reason for my question about how does the OP know if they've had a deadlock. I don't believe they'd be asking if they knew about either TF 1222 or 1205. Some reports of deadlocks do include tracable info and wanted to know where they got the deadlock notification from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WolfgangE SSCertifiable Points: 6328 More actions · Answer 4

You could either have a look at the sql server log, that contains the information THAT a deadlock occurs.

In our system, for example, the applicaton writes any exception into a trace table, that is controlled daily, and it also includes the deadlock-message.

So in our case we know THAT a deadlock occured and when and then we have to think about how to get the details. I guess this thread is quite similar...

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

Since you're on SQL Server 2008, you don't actually have to rely on the traceflags any more. The system_health extended event session will record deadlock graphs that will allow you to go and capture that information. It's running even if you have not enabled the traceflags. I would just use that and skip the traceflags entirely.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning