Closed transaction keeps running...awaiting command

I am debugging an app which blocks many processes in a SQL7 server DB.
The app log writes every transaction "open" and "close".
The weird thing is : when the app logfile says the transaction is
dropped (object closed) the db keeps showing the process "running", in
a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
command status.

We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
IIS side, an equal box for MTS (same patches and updates) and a SQL
Server 7 (on NT4, same fixes ans SPs) database on another box.

Is this normal? Those sleeping processes are blocking other apps and
everything gets slow and messy...the only solution is to kill those
blocking processes.

Thanks![posted and mailed, please reply in news]

Rittercorp (ritter_cl@.yahoo.com) writes:
> I am debugging an app which blocks many processes in a SQL7 server DB.
> The app log writes every transaction "open" and "close".
> The weird thing is : when the app logfile says the transaction is
> dropped (object closed) the db keeps showing the process "running", in
> a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
> command status.
> We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
> IIS side, an equal box for MTS (same patches and updates) and a SQL
> Server 7 (on NT4, same fixes ans SPs) database on another box.
> Is this normal? Those sleeping processes are blocking other apps and
> everything gets slow and messy...the only solution is to kill those
> blocking processes.

No, this is not normal. Apparently your application has a problem with
transaction scope. The fact that your application log looks good proves
little. The application log may know about transaction it starts, but
what about transactions that starts in SQL code, for instance in
stored procedures?

There are a couple of gotchas. For instance, if you call a stored procedure
and that stored procedure starts a transaction, but the application
cancels the procedure before the execution completed, for instance
because of the dreaded "Timeout expired", the transaction started by
the SP is *not* rolled back. The same is true, if the procedure
starts a transaction, and then is aborted because of a reference to
a non-existing table.

On http://www.sommarskog.se/sqlutil/aba_lockinfo.html I have a
utility that gives yuu a lot of information about locks in the server,
including a list of which are the locked objects. This might be helpful
for you to understand where you are leaking transactions. (Although it
might be messy to find out. Because once a process has missed a
COMMIT or ROLLBACK, it will accumulate locks from all over the place.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp