Blocking : Uncommitted Open transactions

Often we come across blocks in SQL server which affects the performance of your production server. There are many information on the net to tune your queries to decrease blocking, or to choose an appropriate isolation level to improve concurrent access. But here I want to mention a rare scenario that sometimes may go unnoticed.

When you are analyzing blocks on SQL server, normally you may run a query like

Select * from sysprocesses where blocked <>0

And you will look at the head blockers.

But I would recomment you sometimes check this

Select * from sysprocesses where status='sleeping' and open_tran>0

If you have a record, you might further want to look at the host,program and the query which was executed recently on the spid.

A developer should thoroughly check entire flow of his SP and should ensure that it leaves no uncommited transactions when completed.

In some funny situations I have seen people enabling implicit transactions in there query analyzer and simply forget to put commit. By the time we report this issue to him, he must have erased and retyped many other queries in the same window. And will not rember what was the query which left the open transaction.