Posted by Erland Sommarskog on 01/31/06 00:37

Robert (robert.j.sipe@boeing.com) writes:
> We had a situation where a user in Access left a drop down menu open all
> night. The drop down menu ran a select query for 13 hours. The query
> only completed when the user ended the session the next day. The focal
> for the application would like SQL Server to terminate the query if it
> takes over 5 or 10 minutes to complete. The select statement left a
> lock on the table and updates and index reorgs were held up. I think
> SQL sees the query as a valid running query and the timeout is ignored.

I think Access is the culprit here. I don't know Access, but I've seen
this problem mentioned before.

When a client runs a query on SQL Server, and gets back a result set, to
be a good citizen, it should consume the entire result set at once (or
cancel it, if it is overwhelmed by row). If the client for some reason
would stop half way through the query, SQL Server will have no choice
but to keep the rows locked, to be able to fulfil its part of the contract.

The issue with Access as I recall is that if a drop-down has more than
around 450 elements, Access do not populate the drop-down all at once,
but will not get row 451 and on, until the user actually scrolls there.

Note that SQL Server does not have any timeout for queries, but they
may run for years if needed. You can define a lock timeout, and there
is also a configuration option called "query wait", but these relates
to how long you wait for resources.

A client can set up a query timeout, and most client APIs has a default
of 30 seconds. However, this timeout is handled by the API. And it's
usually implemented so that it measures the time until the first
data comes.

Thus there is no timeout you can set to prevent this. You need to
change the Access app.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]