Menu
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.
271
Using SQL Server Management Studio In Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.
The connection or login timeout occurs when the initial connection to the database server reaches a predefined time-out period. At this stage, no query has been submitted to the server. These are examples of connection or login time-out error messages: Connection Timeout Expired.
No. You will need to use SQL Profiler. A standard trace with the Lock Timeout event and Deadlock Graph events should do it.
Lock:Timeout Event Class
Deadlock Graph Event Class
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?
107
SQL Server timeouts are initiated from the client-side as Attention Events and are not recorded in the SQL Server Error Log.
You can monitor for Attention Events using:
44
You could also use Event Notifications that get raised 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:
Immediate deadlock notifications without changing existing code 🕗
July 18, 2008
This way you don't have to run Profiler; which can impact performance.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
