SQL Server Timeout Logging

views:

1696

answers:

+2 Q:

SQL Server Timeout Logging

Hello Hopefully this a simple yes/no question.. Are SQL Server timeouts (SELECT queries, in particular) logged in the ERRORLOG file?

Background is a customer with a web site having occasional Request timeout messages, looking to me assuming that the timeout is caused by a database timeout. There are no timeout errors in the ERRORLOG in question.

Thanks Nick

+1 A:

No. You will need to use SQL Profiler. A standard trace with the Lock Timeout event and deadlock events should do it.

Hardware aside (such as enough RAM and fast drives, and appropriate placement of Data and Log files on the appropriate RAID configurations) most timeouts are caused by not having a sufficently 'good' set of indexes for your workload. Do you have index maintenance plans scheduled regularly?

Mitch Wheat 2008-11-17 10:50:15

Thanks for that, I've set a trace going...I don't have regular index maintenance, but I did defrag them last week and the problem is still occurring.

Nick Watts 2008-11-17 11:02:50

as i said profiler trace might cause performance issues if left for a while to run. depending of course on your system load.

Mladen Prajdic 2008-11-17 12:57:35

A properly set up trace should roughly consume less than 5% of CPU and resources. This post will show you how: http://stackoverflow.com/questions/257906/ms-sql-server-2008-how-can-i-log-and-find-the-most-expensive-queries#257944

Mitch Wheat 2008-11-18 14:44:28

you could also use Event Noifications that get rased on the timeout and deadlock events. after it fires you can write it to a table and or send yourself an email. i've shown the general technique here: http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx

this way you don't have to run profiler which can impact performance.

Mladen Prajdic 2008-11-17 11:00:26

ansaurus

tags:

views:

answers:

SQL Server Timeout Logging

related questions