sp_reset_connection是干嘛的？( What does sp_reset_connection do?)

What does sp_reset_connection do?

From http://sqldev.net/misc/sp_reset_connection.htm

version: 1.0.0.1

last updated: 14 April 2005

---

Data access API's layers like ODBC, OLE-DB and SqlClient call the (internal) stored procedure sp_reset_connection when re-using a connection from a connection pool. It does this to reset the state of the connection before it gets re-used, however nowhere is documented what things get reset. This article tries to document the parts of the connection that get reset.

sp_reset_connection resets the following aspects of a connection:

       It resets all error states and numbers (like @@error)

       It stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query

       It will wait for any outstanding I/O operations that is outstanding

       It will free any held buffers on the server by the connection

       It will unlock any buffer resources that are used by the connection

       It will release all memory allocated owned by the connection

       It will clear any work or temporary tables that are created by the connection

       It will kill all global cursors owned by the connection

       It will close any open SQL-XML handles that are open

       It will delete any open SQL-XML related work tables

       It will close all system tables

       It will close all user tables

       It will drop all temporary objects

       It will abort open transactions

       It will defect from a distributed transaction when enlisted

       It will decrement the reference count for users in current database; which release shared database lock

       It will free acquired locks

       It will releases any handles that may have been acquired

       It will reset all SET options to the default values

       It will reset the @@rowcount value

       It will reset the @@identity value

       It will reset any session level trace options using dbcc traceon()

sp_reset_connection will NOT reset:

       Security context, which is why connection pooling matches connections based on the exact connection string

       If you entered an application role using sp_setapprole, since application roles cannot be reverted

To learn more about connection pooling see Pooling in the Microsoft Data Access Components