Monitoring slq server deadlocks

Question

Monitoring slq server deadlocks

Guras

SSCertifiable

Points: 6518
More actions
April 30, 2012 at 10:58 am

#255097

The asp.net application is sending out some database timeout error and I am trying to investigate it. I set the following traces on
dbcc tracestatus
TraceFlag Status Global Session
--------- ------ ------ -------
1204 1 1 0
1222 1 1 0
3605 1 1 0
But when the timeout error is occuring on the web apps it is not getting looged in the errorlog. Do you know why?
I am working on a procedure to send out alert when the deadlock occours.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Henry_Lee SSCrazy Points: 2714 More actions · Answer 1

My guess is the timeout is not the result of an actual deadlock. Instead, it is just a *regular* blocking condition that is now taking longer than expected.

Try setting the "blocked process threshold" then using Profiler (or a server side trace) to catch the event.

sp_configure 'show advanced options',1 ;

GO

RECONFIGURE;

GO

sp_configure 'blocked process threshold',<some number of seconds> ;

GO

RECONFIGURE;

GO

In Profiler the event is under "Errors and Warnings".

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

Deadlocks don't cause timeouts.

Timeouts are a client-side setting and happen when the client decides that SQL Server is taking too long to run a query, and the client aborts the query. A deadlock occurs when two or more processes running in SQL Server hold locks that the other processes need. SQL picks one as a victim and rolls that transaction back, the client will receive error 1205.

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

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 3

If you are trying to get alerted when a deadlock happens, just create a SQL Server Agent Alert for Error number of 1205. That will notify you of a deadlock on the instance.

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

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

Guras (4/30/2012)
dbcc tracestatus
TraceFlag Status Global Session
--------- ------ ------ -------
1204 1 1 0
1222 1 1 0
3605 1 1 0

btw, the only traceflag you need to catch deadlock graphs is 1222. 1204 is the older deadlock info trace flag, for SQL 2000 and before. It returns less info than 1222 does and hence shouldn't be used on SQL 2008. Traceflag 3605 is used to redirect output from some undocumented DBCC commands to the error log, it has nothing to do with deadlocks.

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

Guras SSCertifiable Points: 6518 More actions · Answer 5

GilaMonster (4/30/2012)
Deadlocks don't cause timeouts.
Timeouts are a client-side setting and happen when the client decides that SQL Server is taking too long to run a query, and the client aborts the query. A deadlock occurs when two or more processes running in SQL Server hold locks that the other processes need. SQL picks one as a victim and rolls that transaction back, the client will receive error 1205.

I turned on the trace 1205

DBCC TRACEON (1205,-1)

..and now I got the errror alert

Deadlock encountered .... Printing deadlock information

but the web server application log says timeout error...now I am confused :w00t:

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

Guras (4/30/2012)
GilaMonster (4/30/2012)
Deadlocks don't cause timeouts.
Timeouts are a client-side setting and happen when the client decides that SQL Server is taking too long to run a query, and the client aborts the query. A deadlock occurs when two or more processes running in SQL Server hold locks that the other processes need. SQL picks one as a victim and rolls that transaction back, the client will receive error 1205.
I turned on the trace 1205
DBCC TRACEON (1205,-1)
..and now I got the errror alert
Deadlock encountered .... Printing deadlock information
but the web server application log says timeout error...now I am confused :w00t:

I didn't say traceflag 1205, I said error number 1205. Turn all your traceflags off, and turn JUST 1222 on. That will print any deadlock graphs into the SQL error log

Traceflag 1205 is not somethign you want to run for long periods (if at all) on a busy server.

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