How to detect dead lock from SQL Server side

Question

How to detect dead lock from SQL Server side

Vivien Xing

SSChampion

Points: 12297
More actions
June 10, 2008 at 6:49 pm

#124151

Is there a way to detect the dead lock from SQL Server side including related objects/process info?

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

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
Consultant - Straight Path Solutions
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

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 2

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.

[font="Verdana"]- Deepak[/font]

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

Thank you. That is exactly what I need. I remember something related to trace flags but did not know which trace and how.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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

iv69 Old Hand Points: 386 More actions · Answer 5

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

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 on googles mail service

Vivien Xing SSChampion Points: 12297 More actions · Answer 7

Thank you all. Very good resources. I get more than I expected.

samuel_yanzu SSC Veteran Points: 213 More actions · Answer 8

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! 😀

Cheers!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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! 😀

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, MVP, M.Sc (Comp Sci)
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

samuel_yanzu SSC Veteran Points: 213 More actions · Answer 10

Cheers for the reply and i'll monitor that more closely aswell. Is sys.dm_exec_sessions available on SQL Server 2000?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

No. Use sysprocesses on SQL 2000.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

polkadot SSChampion Points: 13664 More actions · Answer 12

I'm viewing this thread to educate myself on how to identify and resolve deadlocking. The links provided are great but for older (2005 and 2008) versions of SQL Server.

(1) Can you recommend a more modern link (for SQL Server 2008 / 2012) that you've found to be the current 'Bible on deadlock troubleshooting'?

(2) Is this still true:"They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur."

Thanks in advance.

--Quote me