sp_reset_connection - What and When it should be used

Question

"sp_reset_connection"

Can you please help me understand this "sp_reset_connection" and when it should be used and anything to be taken care before using it.

---

Raghavendra Narayana

Answer 1

Do you see it in SQL Profiler?

What does sp_reset_connection do?

http://web.archive.org/web/20100730003952/http://sqldev.net/articles/sp_reset_connection/default.html

/*

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

*/

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Adding on to Uri's answer, sp_reset_connection is an internal stored procedure used by client APIs to manage pooled connections. It is not called directly by application code.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 3

sp_reset_connection is a system proc used by .Net connections.  You should never have a reason to call this directly.

Answer 4

"sp_reset_connection" - will SQL SERVER fire this when Server is restarted ?

---

Raghavendra Narayana

Answer 5

SQL Server provides a procedure sp_reset_connection that is designed to reset the settings and the state. So after SQL Server is started when you use connection pooling (the default), every time you open a connection,  ADO.NET executes sp_reset_connection to make sure you get a "clean" connection before it hands it over to you.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence