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:
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:
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.