What does sp_reset_connection do?

sql sql-server connection-pooling
Rory picture Rory · Feb 27, 2009 · Viewed 26.1k times · Source

sp_reset_connection seems to be called by SQL Server connection pooling, to ensure that connections reused from the pool have most of their settings reset. Does anyone know exactly what it does and doesn't do though?

eg I see from this post that it doesn't reset the transaction isolation level

Answer

Chris Klepeis picture Chris Klepeis · Feb 27, 2009

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

Related questions

What happens to an uncommitted transaction when the connection is closed?

Are they rolled back immediately? Are they rolled back after some period of time? Are they left in an uncommitted state? Is the behavior the same if connection pooling is used and the connections are simply reset?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More