What Exactly Does SQL Server's `sp_reset_connection` Reset and What Does It Leave Untouched?

Resetting Connection States with SQL Server's sp_reset_connection

SQL Server's sp_reset_connection stored procedure is an integral part of connection pooling, ensuring that reused connections from the pool have their settings reset. While it's known to exclude transaction isolation level from its reset operations, it's essential to understand the precise actions it takes.

Aspects Reset by sp_reset_connection:

• Error states and numbers (@@error)
• Termination of child execution contexts
• Waiting for pending I/O operations
• Release of server-held buffers
• Unlock of buffer resources
• Allocation and memory release
• Deletion of work or temporary tables
• Termination of global cursors
• Closure of SQL-XML handles and work tables
• Closure of open system and user tables
• Dropping of temporary objects
• Aborting of open transactions
• Defection from distributed transactions
• Release of shared database lock
• Release of acquired locks
• Reset of SET options to defaults
• Reset of @@rowcount and @@identity values
• Reset of session-level trace options

Aspects Excluded from Reset by sp_reset_connection:

• Security context (requiring matching connection strings for pool reuse)
• Application roles (cannot be reverted)
• Transaction isolation level

The above is the detailed content of What Exactly Does SQL Server's `sp_reset_connection` Reset and What Does It Leave Untouched?. For more information, please follow other related articles on the PHP Chinese website!