For one of our SQL servers there has been a proc which creates a blocking mess:
That stored proc completes under a sec most of the times but somehow is leaving transaction open. What i see from sp_whoisactive is status = sleeping and open tran 1 for duration of approx 5-6 minutes. In this duration heaving blocking chain shows up.
This SP do not have any transaction like BEGIN and END TRAN. It does some basic select col,col2,col3.... into #temptables from table1 inner join table 2 ... and then select from that #temptable
While we are checking from app why there could be a transaction left open, i was reading in such scenarios to use XACT ABORT ON in SP itself. But when there is no transaction involved how XACT ABORT setting will help in this case?