FIX: Dropped Net Session Not Detected During Long Query

ID: Q124949

The information in this article applies to:

Microsoft SQL Server version 4.2x

BUG# NT: 966 (4.21)

SYMPTOMS

A query can continue to run on SQL Server even after the client reboots and the network session has dropped. The query will acquire whatever type of locks are appropriate for the query type, which in some cases can block other users. Unless it becomes blocked on another connection's lock, the query will terminate when it has run to completion or when it needs to send results back to the nonexistent client. The query can usually be terminated with the Transact-SQL KILL command.

CAUSE

If a client is running a long query that does not return results for a while, then the net session is dropped because the client reboots, the query can continue to run. An example of this type of query would be:


   SELECT COUNT(*) FROM LARGETABLE

If the query became blocked on another connection's lock, this could also prevent it from returning results. If in this state, the client running the query reboots, the query will continue to run even though its network session is terminated. This is caused by SQL Server not noticing the network session termination. Whenever the query begins to send results back to the nonexistent client, SQL Server will notice the network session is gone and terminate the query.

WORKAROUND

This problem only happens infrequently, as two fairly rare simultaneous events must occur to reproduce it.

A long-running query that does not return results.

The same client running the query must abruptly terminate in an uncontrolled fashion, rather than logging out or canceling the query with dbcancel().

In cases where this happens, the workaround is to use the Transact-SQL KILL command to terminate the query. A well-designed application should always allow users to cancel a query at any point during execution. These applications will generally not see this problem.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.21. This problem was corrected in the latest U.S. Service Pack for SQL Server version 4.21a. For information on obtaining the Service Pack, please contact your primary support provider.

Additional query words: Windows NT opendsnt

Keywords :
Version :
Platform :
Issue type :