Systems Engineering and RDBMS

sp_reset_connection

Posted by decipherinfosys on February 1, 2008

While using SQL Server Profiler to trace out events, you might have noticed calls to the system stored procedure: sp_reset_connection. The only thing that is mentioned in BOL is:

The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool.

So, what exactly does this system stored procedure do? This system stored procedure gets executed when the ODBC or the OLEDB connection pool takes a connection that has been released to the connection pool and is still active. Before re-using the connection from the pool, this system stored procedure gets executed to make sure that the connection options are re-set. We can see this by a simple example in SQL Server Management Studio:

SET NOCOUNT ON

You can now see which of these options are set in your environment:

DECLARE @options INT

SELECT @options = @@OPTIONS

IF ( (512 & @options) = 512 ) SELECT ‘NOCOUNT’

And you will get:

——-
NOCOUNT

And now, if we re-set the connection (in SQL Server Management Studio, you can simulate it by right clicking and then Connection/Change Connection and then connect to the same instance as before) – and then when you execute the above, you will see that NOCOUNT will not be present since the connection has been re-set.  The same thing happens when sp_reset_connection gets executed. There are a lot of things that are re-set by this stored procedure like re-setting of the SET command options, re-setting of @@identity, @@rowcount, @@trancount etc., aborting any open transactions, closing out any open SQLXML handles, releasing any memory that was owned up by the connection etc.. Here is a link to a site which states what does and does not get re-set by this system stored procedure:

http://www.sqldev.net/misc/sp_reset_connection.htm

This is a lightweight stored procedure so there is no cause of worry to see it along with your Audit Login and Logout events.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: