-1

Which time out value does SQL server take into consideration while executing a query or stored procedure? The application timeout is set at 20 minutes. The SQL query/command timeout is set at 10 minutes. Then there is an F5 (load balancer) timeout set at 5 minutes. The SQL queries continue to run for around 40-50 minutes bypassing all the timeout values, even after the request has timed out. Afterward somebody has to kill the query. These long running queries are actually killing our system performance and filling up the SQL Server DB transaction log files.

squillman
  • 37,618
  • 10
  • 90
  • 145
Mahesh
  • 1
  • 1
  • 2

2 Answers2

0

SQL Server does not enforce any sort of query timeout by default. A query could run forever.

Usually, client timeout values merely mean that the client will give up and return an error. The client might have lost all connection to the database server, and trying to clean up after itself would likely be a lost cause. In the case where a client does try to kill a query, you may run into a situation where rolling back the work that the query did may take a long time. This is often interpreted as the query "still running". This would only occur if you are modifying data (INSERT, UPDATE, DELETE) or possibly if you are running some sort of large aggregations that use tempdb.

10 minutes is a long time for an IIS page.

I would look for blocking between connections first. A delete statement that removes a single row can block any select on a table, in certain circumstances. You should be able to find questions here on ServerFault to help you do that.

I would suggest reviewing your queries, tables, indexes and index maintenance to make sure that everything follows best practices. This is a big topic, but there are resources here on ServerFault and on nearly any SQL Server adminstration site to help you with it.

Darin Strait
  • 2,012
  • 12
  • 6
0

Your command timeout is probably signaling an Attention event to the server. You can verify this with a packet capture or SQL profiler trace. The Attention event is what normally causes SQL server to abort any activity in progress.

However, if you are signaling an Attention event, this does not always work very well. You may want to try setting XACT_ABORT for the default connection values, or modify the existing code to use XACT_ABORT.

http://blogs.msdn.com/b/pamitt/archive/2010/11/07/attention-events-causing-open-transaction-and-blocking.aspx

Greg Askew
  • 34,339
  • 3
  • 52
  • 81