Deadlocks not showing up in my SQLServer error log 1

[bessebo]

We are getting deadlocks at least once a day and these deadlocks are not showing up in the SQLServer error log or in the Event Viewer for that machine. Is there some setting that I have to change to see these errors. We are trying to isolate the problem but can't even trap the error.

Thanks,
Bessebo

 

[AshleyRickards]

Use Profiler and save it to a table. This way you can go back and see all activity. Be careful it can grow quickly and take up disk space. Also will use system resources. Try running for a couple of hours and capture the deadlock. Ashley L Rickards
SQL DBA

 

[tlbroadbent]

Ashley is correct. Profiler can be very helpful as can sp_lock, sp_lock2 and sp_who2 in identifiying blocking and blocked connections. Check faq183-714 for links to articles about identifying and reducing blocking/locking problems. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bessebo]

I understand that I can use Profiler to capture a trace but I never know when this deadlock will occur and I don't want to chew up exhorbitant amounts of disk space. Shouldn't this error show up in the Error Log?

Bessebo

 

[tlbroadbent]

By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

Write deadlock info to the SQL Server error log:
DBCC TRACEON(-1, 1204, 3605)

Turn it off:
DBCC TRACEOFF(-1, 1204, 3605)

See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on.

http://msdn.microsoft.com/library/en-us/trblsql/tr_servdatabse_5xrn.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bessebo]

Thanks Terry,
I initiated that and now I'm just waiting for the deadlock to occur so I can get some more descriptive info. You're the best...

Bessebo