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 12»»

How to detect dead lock from SQL Server side Expand / Collapse
Author
Message
Posted Tuesday, June 10, 2008 6:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 2:49 PM
Points: 1,279, Visits: 2,203
Is there a way to detect the dead lock from SQL Server side including related objects/process info?
Post #514877
Posted Tuesday, June 10, 2008 6:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 11,157, Visits: 12,899
I'm not sure what you mean when you say from the SQL Server side. You can use the sp_lock stored procedure and in 2005 I believe you can use the DMV's to see this information.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #514878
Posted Tuesday, June 10, 2008 9:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:14 PM
Points: 402, Visits: 598
Please refer this link which might help you begin with analyzing deadlock.
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server
You need to make use of the trace flag 1222 and 3605 to analyze the same.


- Deepak
Post #514909
Posted Wednesday, June 11, 2008 9:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 2:49 PM
Points: 1,279, Visits: 2,203
Thank you. That is exactly what I need. I remember something related to trace flags but did not know which trace and how.
Post #515627
Posted Thursday, June 12, 2008 12:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
You actually don't need trace flag 3605. Just 1222 or 1204 is enough to write the deadlock info into the error log.

1204 puts the deadlock graph into the error log in text. 1222 (SQL 2005 or higher) puts the deadlock graph into the error log in xml.

On SQL 2005, you can also use profiler to catch the deadlock graphs. I still prefer the traceflags though.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #515674
Posted Thursday, June 12, 2008 1:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:23 AM
Points: 71, Visits: 157
If You are using SQL 2005 there is an Event Notification that can be used -DEADLOCK_GRAPH
you can use Service Broker to log every deadlock info into table ,information is complete and useful.
Ivan



Post #515686
Posted Friday, June 13, 2008 1:16 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:19 AM
Points: 4,320, Visits: 6,113
Here (again) is the Bible on deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #517001
Posted Sunday, June 15, 2008 8:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 2:49 PM
Points: 1,279, Visits: 2,203
Thank you all. Very good resources. I get more than I expected.
Post #517339
Posted Monday, June 16, 2008 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:30 AM
Points: 5, Visits: 651
I concur! I am having major problems with one of your applications at the moment and this will be a good way determining whether deadlocks are correlating to any of the timeouts and MS Access "not responding" screens that they are experiencing. I hope it is then I can look forward to getting the developers to clean up their dodgy code! :D

Cheers!
Post #517653
Posted Monday, June 16, 2008 9:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
samuel_yanzu (6/16/2008)
I concur! I am having major problems with one of your applications at the moment and this will be a good way determining whether deadlocks are correlating to any of the timeouts and MS Access "not responding" screens that they are experiencing. I hope it is then I can look forward to getting the developers to clean up their dodgy code! :D


Sounds like you are having long duration blocking, not deadlocks. If a deadlock occurs, one of the session involved in the deadlock will get an error along the lines of 'Your session was involved in a deadlock and was picked as the deadlock victim'. The other session will probably not notice anything.

Check the sys.dm_exec_sessions and sys.dm_exec_requests DMVs and see if you have a lot of sessions with a blocked_by that's not 0



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #517658
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse