The Iron Yuppie

I've been doing some coding on the side, and the first thing I do whenever I run into a problem or question is type as much of the original error text into a search engine. So I was chugging along recently, and I was looking at the SQL Profiler, and saw a lot of 'exec sp_reset_connection' in there. I was wondering if I was doing something wrong, so off I went to live.com (http://search.live.com/results.aspx?q=sql.server.profiler+exec+sp_reset_connection&src=IE-SearchBox):

• Search: "sql.server.profiler exec sp_reset_connection"

1 result.

• Microsoft SQL Server - Examining SQL Server Trace Files - SQLTeam.com

• sp_reset_connection. If you're using connection pooling you'll see lots ... P3 int set @P3=1 declare @P4 int set @P4=1 exec sp ... What's New in SQL Server Profiler 2000 (sqlmag.com) on 5/2/2001

Uh oh, maybe it's time to alter the search terms. But that brief sentence after the stored procedure name looks promising. Let's go to the video tape!

Examining SQL Server Trace Files: http://www.sqlteam.com/item.asp?ItemID=24658

If you're using connection pooling you'll see lots and lots of calls to sp_reset_connection. Typically you'll see one before each SQL batch or stored procedure. ODBC and OLEDB use this to reset a connection before it's "passed around" in the connection pool. Gert Drapers has a good description of what sp_reset_connection resets. Basically I'd just ignore this in the trace (which is what I do in ClearTrace).

Wow. Exactly what I wanted.

For the curious, I also ran it against Google (http://www.google.com/search?q=sql.server.profiler+exec+sp_reset_connection&rls=com.microsoft:*&ie=UTF-8&oe=UTF-8&startIndex=&startPage=1) and came up with 7 results, with the above link being the first. Odd part is that none of the rest of the links answered any of my questions and in fact are just distracting.

posted by Iron Yuppie  # 2/14/2007 10:51:00 AM