SQLDev.Net

What does sp_reset_connection do?

Data access API's layers like ODBC, OLE-DB and System.Data.SqlClient all 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 can not be reverted