Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find out cause of deadlock? Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 11:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 11:04 AM
Points: 86, Visits: 378
Hi All,
2days ago we have a dead lock.
how to find out, for what cause the dead lock got occured?
Post #1462285
Posted Tuesday, June 11, 2013 4:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1462416
Posted Wednesday, June 12, 2013 9:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:36 AM
Points: 199, Visits: 740
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.
Post #1462694
Posted Wednesday, June 12, 2013 11:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1462755
Posted Wednesday, June 12, 2013 11:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:36 AM
Points: 199, Visits: 740
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...
Post #1462767
Posted Friday, June 14, 2013 2:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1463423
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse