Reconciling @@TRANCOUNT Running SS2005.  I have a script that calls procedureA which in turn calls procedureB.  

procedureA has no explicit transactions.  procedureB is split up into several small blocks of non-nested BEGIN and COMMIT TRANSACTION.  

I have peppered procedureB with PRINT statements to show me the value of @@TRANCOUNT.  It's always at 0 or 1.  

But when I check master..sysprocesses it shows open_tran at 1 or 2.  Where this extra transaction is coming from?  

I am posting the 2 procedures below.  pr_Get12Relatives has been stripped of most of its content for clarity as it contains 1600 lines!  It just repeats the same behavior as it searches for different types of relationships.

procedureA = pr_GetRelatives

procedureB = pr_Get12Relatives
This thread answers my question..

When a DML statement is currently being executed in a session the open_tran and @@trancount is always at least 2.

For example

Connection 1

CREATE TABLE T( X INT PRIMARY KEY)

GO

BEGIN TRAN

INSERT INTO T VALUES(1)

WAITFOR DELAY '00:01'

ROLLBACK


Connection 2

INSERT INTO T VALUES(1) /*Blocks on Connection 1*/


Connection 3

(look at blocked connection 2)

SELECT lastwaittype,
       open_tran
FROM   master..sysprocesses
WHERE  spid = &lt;spid of connection 2&gt;


Results

+----------------------------------+-----------+
|           lastwaittype           | open_tran |
+----------------------------------+-----------+
| LCK_M_X                          |         2 |
+----------------------------------+-----------+