The sp_reset_connection stored procedure is not well documented since it's an internal stored procedure and never called directly. Be aware that it may change or be removed in the future without notice. That being said, it is useful to know a little about the internals for troubleshooting and correlating activity trace data.
The purpose of the proc is to restore connection environment state to that of a newly opened connection to support connection pooling. The client API specifies whether a connection is to be reset by setting either the RESETCONNECTION or RESETCONNECTIONSKIPTRAN bits of the TDS protocol status field when a request is issued on a newly reused connection from the pool. SQL Server internally invokes the sp_reset_connection RPC you see in a trace when either of those flags are set.
The TDS protocol documentation desribes these flags so we can infer this is what sp_reset_connection does under the covers. Below is the an excerpt from the documentation:
RESETCONNECTION (Introduced in TDS 7.1) (From client to server) Reset
this connection before processing event. Only set for event types
Batch, RPC, or Transaction Manager request. If clients want to set
this bit, it MUST be part of the first packet of the message. This
signals the server to clean up the environment state of the connection
back to the default environment setting, effectively simulating a
logout and a subsequent login, and provides server support for
connection pooling. This bit SHOULD be ignored if it is set in a
packet that is not the first packet of the message.
This status bit MUST NOT be set in conjunction with the
RESETCONNECTIONSKIPTRAN bit. Distributed transactions and isolation
levels will not be reset. 0x10
RESETCONNECTIONSKIPTRAN (Introduced in TDS 7.3) (From client to
server) Reset the connection before processing event but do not modify
the transaction state (the state will remain the same before and after
the reset). The transaction in the session can be a local transaction
that is started from the session or it can be a distributed
transaction in which the session is enlisted. This status bit MUST NOT
be set in conjunction with the RESETCONNECTION bit. Otherwise
identical to RESETCONNECTION.
The low-level client API (SqlClient here) indirectly controls the behavior of sp_reset_connection by setting those flags as needed. When a connection is reused outside a TransactionScope, the RESETCONNECTION flag is set as can be seen in this network packet trace (Wireshark):
Below is a trace of the first request issued on a reused connection from within the scope of a TransactionScope using block:
As you can see, the reset within TransactionScope will not rollback the transaction. Consequently, even if your ORM follows an open/execute/close pattern for data access, your outer TransactionScope transaction context will be honored. The transction will be committed as long as you invoke TransactionScope.Complete before exiting the transaction scope. You can include a TM Commit Tran completed event to your Profiler trace to see the commit when the TransactionScope exits.
BTW, Profiler/SQL trace is deprecated. Use Extended Events instead going forward. Also, as suggested in the other answer, be sure to specify the desired transaction isolation level (e.g. read committed) when you use TransactionScope. The default is serializable which can have side effects like unnecessary blocking and deadlocks. I suggest you use serializable only if you specifically need that strict level of isolation.
