SQL sp_reset_connection 有什么作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/596365/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What does sp_reset_connection do?
提问by Rory
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?
sp_reset_connection 似乎由 SQL Server 连接池调用，以确保从池中重用的连接的大部分设置都已重置。有没有人确切地知道它做什么和不做什么？
eg I see from this postthat it doesn't reset the transaction isolation level
例如我从这篇文章中看到它不会重置事务隔离级别
回答by Chris Klepeis
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.
数据访问 API 的层（如 ODBC、OLE-DB 和 SqlClient）在重新使用连接池中的连接时调用（内部）存储过程 sp_reset_connection。它这样做是为了在重新使用之前重置连接的状态。
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:
sp_reset_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()
- 它重置所有错误状态和数字（如@@error）
- 它停止作为执行并行查询的父 EC 的子线程的所有 EC（执行上下文）
- 它将等待任何未完成的未完成 I/O 操作
- 它将通过连接释放服务器上的任何保留缓冲区
- 它将解锁连接使用的任何缓冲区资源
- 它将释放连接拥有的所有分配的内存
- 它将清除连接创建的任何工作表或临时表
- 它将杀死连接拥有的所有全局游标
- 它将关闭所有打开的 SQL-XML 句柄
- 它将删除任何打开的 SQL-XML 相关工作表
- 它将关闭所有系统表
- 它将关闭所有用户表
- 它将删除所有临时对象
- 它将中止打开的交易
- 入伍时，它将从分布式事务中脱离
- 它将减少当前数据库中用户的引用计数；释放共享数据库锁
- 它将释放获得的锁
- 它将释放可能已获得的任何句柄
- 它将所有 SET 选项重置为默认值
- 它将重置@@rowcount 值
- 它将重置@@identity 值
- 它将使用 dbcc traceon() 重置任何会话级跟踪选项
sp_reset_connection will NOT reset:
sp_reset_connection 不会重置：
- 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
- 安全上下文，这就是连接池根据确切的连接字符串匹配连接的原因
- 如果您使用 sp_setapprole 输入应用程序角色，因为应用程序角色无法恢复
- 事务隔离级别
回答by Mark Struzinski
From this forum post:
从这个论坛帖子：
The sp_reset_connection stored procedure is used to reset a connection so that when it is used in a pool, nothing from a previous session is stored that is connection-specific.
sp_reset_connection 存储过程用于重置连接，以便在池中使用它时，不会存储任何特定于连接的先前会话中的任何内容。
