Why are sysprocesses left open on SQL Server for a long time?

Question

I've noticed a lot of db connections left open on my production SQL Server, some for a very long time. Many of these have an open transaction. As seen in the query:

select * from sysprocesses where open_tran>0

They are in status = "sleeping", cmd = "AWAITING COMMAND".

The connections are opened using NHIBERNATE as follows:

For<ISessionFactory>().Singleton()
    .Use(() => DataContext.GetSessionFactory());

For<ISession>().Transient()
  .Use(context => context.GetInstance<ISessionFactory>().OpenSession());

Some sessions use a transaction scope: _transactionScope = new TransactionScope();

Some create a transaction:

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

The transaction and/or transactionScope are disposed afterwards.

Why are the connections still open? And is it a problem, if nothing is being blocked?

The db is partly at the beck and call of the app that made it.. an app can make a db call, and leave it open, or it can open, do and close.. many bad web coders open dbs and not close them and it takes a while to notice to close it — BugFinder

TheGameiswar TheGameiswar · Accepted Answer · 2016-09-26T14:17:41

They are in status = "sleeping", cmd = "AWAITING COMMAND".

when you see a row in sysprocesses with a status of sleeping,that means this connection is active ,but not being used..This also happens due to connection Pooling mechanism used by SQLServer..

Awaiting command can be explained by below example..

Session 1: start this transaction

begin tran
insert into sometable
select some columns

Now when you check the status of session1 in sys.processes/session DMV,you can see that as Awaiting command..since ,you didn't committed it.

One more reason of awaiting command can be when you start a transaction and wait for user input like

begin tran
update t
set t1.a=<<some input from user>>--waiting
where t1.a=oldvalue
commit

And is it a problem, if nothing is being blocked?

As long as you see,nothing is blocked,sleeping and sessions are not holding any locks and you don't see any open transactions, you don't need to worry.This is explained by Bob Dorr in this article..

The question usually arises around a session that is holding locks and its state is sleeping / awaiting command. If the client has an open transaction and the client did not submit a commit or rollback command the state is sleeping / awaiting command. I see this quite often with a procedure that times out.

Create proc myProc
As
Begin tran
Update authors ….
 Waitfor delay ’10:00:00’   — time out will occur here  (simulates long workload)
 rollback
go

When run from the client with a 30 second query timeout the transaction will remain open because the client indicated it wanted to ‘cancel execution’ and do no further processing.

To get automatic rollback in this situation transaction abort must be enabled. You now have an open transaction with a SPID sleeping/awaiting command. The situation can be caused by many other variations but it is always a situation where the SQL Server is waiting for the next command from the client

Why are sysprocesses left open on SQL Server for a long time?

2 Answers