What does sp_reset_connection do?

by

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

More Related Contents:

  1. What happens to an uncommitted transaction when the connection is closed?
  2. Get Parent with multiple children in desc order
  3. How can I schedule a job to run a SQL query daily?
  4. Convert Month Number to Month Name Function in SQL
  5. SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]
  6. Reset identity seed after deleting records in SQL Server
  7. How to avoid the “divide by zero” error in SQL?
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Limit on the WHERE col IN (…) condition
  10. Removing leading zeroes from a field in a SQL statement
  11. How to simulate UNPIVOT in Access?
  12. Get day of week in SQL Server 2005/2008
  13. Real life example, when to use OUTER / CROSS APPLY in SQL
  14. Getting result of dynamic SQL into a variable for sql-server
  15. How to get script of SQL Server data? [duplicate]
  16. Better techniques for trimming leading zeros in SQL Server?
  17. SQL Server: Extract Table Meta-Data (description, fields and their data types)
  18. Dynamic Pivot Table in SQL Server
  19. Optimized SQL for tree structures
  20. How to insert a blob into a database using sql server management studio
  21. Joining multiple tables in SQL
  22. Get the character between first 2 special character in SQL
  23. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  24. SQL count consecutive days
  25. Inner Joining three tables
  26. SQL – How do I get only the numbers after the decimal?
  27. How to retrieve records for last 30 minutes in MS SQL?
  28. Connecting to Oracle Database using Sql Server Integration Services
  29. Why do SQL Server Scalar-valued functions get slower?
  30. Executing SQL query on multiple databases

Leave a Comment