snippetMinor
How to debug deadlocks in SQL Server 2008 R2?
Viewed 0 times
how2008sqldebugdeadlocksserver
Problem
I'm using SQL Server 2008 R2 that automatically lock database items based on my queries.
Sometimes this deadlocks - two threads with different queries never ends.
Is there any way to debug how SQL Server locks and unlocks resources? I have tried SQL Trace but it has no information about resources locks.
Sometimes this deadlocks - two threads with different queries never ends.
Is there any way to debug how SQL Server locks and unlocks resources? I have tried SQL Trace but it has no information about resources locks.
Solution
"Sometimes this deadlocks - two threads with different queries never ends"
What you are experiencing is blocking, not deadlock. When a deadlock condition occurs, SQL server automatically detects it and choose one of the queries as a victim and throws error 1205.
If you are really experiencing deadlock errors, turn on trace flag 1222. It will log the deadlock errors to the SQL server log.
If you are experiencing blocking, configure the "block process threshold" (http://msdn.microsoft.com/en-us/library/ms181150(v=SQL.90).aspx)
Also see http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/
What you are experiencing is blocking, not deadlock. When a deadlock condition occurs, SQL server automatically detects it and choose one of the queries as a victim and throws error 1205.
If you are really experiencing deadlock errors, turn on trace flag 1222. It will log the deadlock errors to the SQL server log.
If you are experiencing blocking, configure the "block process threshold" (http://msdn.microsoft.com/en-us/library/ms181150(v=SQL.90).aspx)
Also see http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/
Context
StackExchange Database Administrators Q#25030, answer score: 9
Revisions (0)
No revisions yet.