SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlocks - SQL Server Monitors for Deadlocks Right?


Deadlocks - SQL Server Monitors for Deadlocks Right?

Author
Message
ReamerXXVI
ReamerXXVI
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 316
Hi,

Can anyone please explain this to me; SQL Server has as dedicated thread that monitors for deadlocks and if one is found arbitrarily rolls back one of the transactions right? So my question is if this thread is constantly running, monitoring for deadlocks, why do they still occur?

Thanks!
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2372 Visits: 2243
Deadlock occurs when 2 or more processes block one another permanently and unless deadlock situation has been resolved no process can go ahead and complete it's task.
Monitoring is just monitoring, it's not a prevention of any DB situation\occurrence.

--
SQLBuddy
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6670 Visits: 7394
SQL server makes you aware the deadlock occurred, but it's not causing the deadlocks, your code is causing the deadlocks (whether that be TSQL, application code, etc.

Deadlocks typically occur because two or more processes are attempting to lock/update the same resource - (Reference: http://www.mssqltips.com/sqlservertip/1036/finding-and-troubleshooting-sql-server-deadlocks/).

If you want to see more detailed information on what caused the deadlock and which one was the deadlock victim, you can have them written to the error log - you can do this by turning on the proper trace flag:
DBCC TRACEON(-1, 1204, 3605)



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39763 Visits: 32640
You can gather information on deadlocks on SQL Server 2008 and better without ever setting a trace flag. Just use the system_health extended event session that is running automatically on your system. It captures deadlock graphs. For a lot more information about deadlocks, I'd suggest this article by Gail Shaw.

----------------------------------------------------
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87209 Visits: 45271
MyDoggieJessie (3/12/2014)
If you want to see more detailed information on what caused the deadlock and which one was the deadlock victim, you can have them written to the error log - you can do this by turning on the proper trace flag:
DBCC TRACEON(-1, 1204, 3605)



3605 isn't needed. That traceflag just causes some commands like DBCC PAGE to write their output into the error log.
1204 is for SQL 2000 and below, it doesn't give as much information as the newer traceflag does.

For deadlock monitoring on SQL 2005 and above:
DBCC TRACEON(1222,-1)



Or the system health event session on SQL 2008 and above.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search