What does sp_reset_connection do?

Solution 1:

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.

There does not appear to be official documentation on what things get reset, but here is an unofficial list.

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 can not be reverted
• The transaction isolation level