sp_reset_connection resets the following aspects of a connection:

All error states and numbers (like @@error)

Stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query

Waits for any outstanding I/O operations that is outstanding

Frees any held buffers on the server by the connection

Unlocks any buffer resources that are used by the connection

Releases all allocated memory owned by the connection

Clears any work or temporary tables that are created by the connection

Kills all global cursors owned by the connection

Closes any open SQL-XML handles that are open

Deletes any open SQL-XML related work tables

Closes all system tables

Closes all user tables

Drops all temporary objects

Aborts open transactions

Defects from a distributed transaction when enlisted

Decrements the reference count for users in current database which releases shared database locks

Frees acquired locks

Releases any acquired handles

Resets all SET options to the default values

Resets the @@rowcount value

Resets the @@identity value

Resets any session level trace options using dbcc traceon()

Resets CONTEXT_INFO to NULL in SQL Server 2005 and newer [ not part of the original article ]