FIX: Dropped Net Session Not Detected During Long Query

 Last reviewed: December 19, 1997
Article ID: Q124949
4.21 WINDOWS kbbug4.21 kbprg kbtshoot kbfix4.21a.sp2

The information in this article applies to:

  • Microsoft SQL Server, version 4.21

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.

  1. A long-running query that does not return results.

  2. 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 reference words: 4.21 Windows NT opendsnt
KBCategory: kbprg kbtshoot
KBSubcategory: SSrvTran_SQL SSrvWinNT

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 19, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.