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

close all existing connections and processes to a database

Dear all

I created this trigger on a table that i think failed while execution. I tried to modify it and run it again but it seems that i cant do that. If i try and delete the database i also cant - saying that it is still in use. But i am not using it and ther are no other users connected to it. I think the trigger has probably hit a loop and that is holding the link.

To close that i know that a solution would be to restart the SQL server instance but that would be a bit hard since the SQL server where my test database resides is a production server and has few other databases that are important and few users use them.

Is there any way through a SQL statement that there can be forced a delete? Or force close all the connections? Or force close all the processes without actually restarting the SQL server instance.

I have tried all options that were offered on some other forums like forcing it to a single user but even that operation can not be performed saying that the database is still in use.

Thank you so much for all your help and time.

Sincerely

Dan

You could cycle through the SPID's in master.dbo.sysprocess and KILL off any SPID's using the test database_id.

And then you have learned why you 'should' not 'play' on a production server. Create yourself a local server for experiementation and testing. Get an 'old', decommissioned desktop and convert it to a test server. There is no excuse for mucking up a production server.