Shaul Behr Shaul Behr - 1 year ago 62
ASP.NET (C#) Question

How to troubleshoot old Sql connections with open_tran > 0?

We have an ASP.NET API web site which connects using NHibernate to a SQL Server.

The problem we are experiencing is that gradually throughout the day, the number of connections to the SQL server creeps up, and there are many connections that do not appear to be returned to the pool. By this, I mean that if I run the following query:

select * from master..sysprocesses s where datediff(minute, s.last_batch, getdate())>10

the number of rows returned just keeps climbing. Nothing in the API should be taking 10 minutes to complete. And there are connections in there from hours ago.

Here's another clue: the
column of all these rows has a value of 1. So it seems to me that somewhere inside the API call, we're creating a transaction boundary, and that transaction is never being closed. Perhaps DTC may have a hand in this (we sometimes do connect to more than one database in a call).

The thing is, I haven't a clue how to troubleshoot this further. I've tried running
on the rogue spids, and there's nothing consistent between them.

What are some of the anti-patterns/other possible causes that might lead to this behavior?

Update: here's how the DB connection is being created. We're using StructureMap for Dependency Injection. We create two DB connections on each unit of work: one "normal" connection for regular read/write access, and an "uncommitted" connection that runs in a transaction with "ReadUncommitted" access (we were having a problem with table locking when reading from large tables).

Here's the code from the DI Registry:

For<ISession>().Transient().Use(context => context.GetInstance<ISessionFactory>().OpenSession());
For<ISessionUncommittedWrapper>().Transient().Use(context => new SessionUncommittedWrapper { Session = context.GetInstance<ISessionFactory>().OpenSession() });

Then, inside the unit of work middleware, we create a
(with a
block, of course), which takes an
and an
in the constructor. In the
method, we have:

_uncommittedTransaction = SessionUncommittedWrapper.Session.BeginTransaction(IsolationLevel.ReadUncommitted);

which gets disposed (along with the
) in the

Answer Source

I eventually found the problem.

The way I found the problem was by creating a logging table that tracked the creation and disposal of Sessions, along with the URI of the endpoint called. By querying all the undisposed connections, I found that in every case where the connection was not disposed, the path began with "/signalr".


Since the OWIN middleware was proactively creating the Sql connections, it was also doing so for SignalR, which in its nature, keeps the transaction open! So every client that logged in with SignalR was hogging two Sql connections.

I made the appropriate changes to exclude SignalR connections from the middleware, and now we have no more hanging Sql connections.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download