September 25, 2002 at 4:35 pm
I am running queries in my query analyzer. every one of these eventually fails or returns a recordset. regardless of what the result is, on the server's list of processes, they continue to be listed as still running, even when the process appears to be complete, as opposed to hung up. after enough of these pile up, they clog my resources, preventing future tasks.
my question: why are these takss remaining open? i can't run the KILL command; so it's a real hassle for my system administrator to have to kill all these tasks that are actually complete.
is there something i can put in my code to make the procedure end by itself? I'd prefer to not have to close my SQL connection after each query
September 25, 2002 at 4:54 pm
Running in a transaction? Normally once you've gotten all the results back the connection stays open but sysprocesses will say the spid is sleeping.
Andy
September 25, 2002 at 4:56 pm
Do you have explicits transactions on?
September 25, 2002 at 5:44 pm
no. how can i do implement explicit transactions?
September 25, 2002 at 5:53 pm
With explicits transaction, you must finish the transaction with either commit or rollback. If you don't finish the transaction, then it will remain open. I don't have sql here but i think with select @@opentrans you can check out how many transaction are still open waiting for commit.
I don't remember the exact command but with Set implicit_transaction ON/OFF to change the value. This setting is for the active connection only.
September 26, 2002 at 11:08 am
select @@TRANCOUNT will give you the current open transaction count. Try executing: COMMIT WORK
If you get error"
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Then open transactions are not the cause.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply