ASP, SQL Process, and SQLDMO

Hi all,

Our ASP scripts send SQL statements (as a stored procedure) to SQL Agent to process the requested jobs in the background. After the job execution, the Process ID stays in the server and waits for next command with sleeping status. Since this process does not go away, next job execution adds another process and eventually, the server is overloaded with these processes and dies. (Under Process Info of SQL Svr Enterprise Management, there are hundreds of process IDs with sleeping status and awaiting command status. Most of these process are executed by SQLDMO because Application column indicates as SQLDMO_xxx (xxx denotes for digits).)

We make sure of opening and closing the connectinos from the scripts, but this problem does not go away. Does anyone have any ideas how to solve this problem? How come those processes don't go away after the job execution? Any comments would be greatly appreciated.

BTW, we use SQL Server 2000.

Thanks in advance,
kerroppa

Are you explicitly disconnecting or just destroying the object?

i.e.

Set SQLServer = Nothing

instead of

SQLServer.DisConnect



Jeff

Hmm... I think we are only destroying the object; howenver, we close the connections by doing;

SQLServer.Close

Is this different from "SQLServer.DisConnect"?

TIA,
kerroppa

I'm not positive how the object.close behaves, but I know destroying the object doesn't release the connection, standard DMO behavior. You might try explicity issuing the disconnect and see if that resolves your issue.

Jeff

Hi,

This may sound a silly question, but here goes...

We have following objects created to send jobs to SQL Agent;

------
SET theServer = Server.CreateObject("SQLDMO.SQLServer")
SET theJob = Server.CreateObject("SQLDMO.Job")
SET theStep = Server.CreateObject("SQLDMO.JobStep")
SET theJobSchedule = Server.CreateObject("SQLDMO.JobSchedule")
SET sendAfter = Server.CreateObject("SQLDMO.JobStep")
SET failed = Server.CreateObject("SQLDMO.JobStep")

theServer.Connect...

...
-----

And when we destroy objects/close the connection, we do this.

-----
SET theServer = Nothing
SET theJob = Nothing
SET theStep = Nothing
SET theJobSchedule = Nothing
SET sendAfter = Nothing
SET failed = Nothing
SET allJobs = Nothing
SET theResults2 = Nothing
theConnection2.DisConnect
SET theConnection2 = Nothing
-----

Since only theConnection2 object was explicitly opened the connection to the database, we also explicitly terminate the connection. For other objects, we can only destory them, right?

Thanks in advance,
kerroppa

Yes, but disconnect before you destroy any other objects.

Jeff

Made a mistake on my part. It should've been like this;

-----
theServer.disConnect
SET theServer = Nothing
SET theJob = Nothing
SET theStep = Nothing
SET theJobSchedule = Nothing
SET sendAfter = Nothing
SET failed = Nothing
-----

Thanks Jeff.
kerroppa

Hi,

The properties of leftout processes indicate that "exec msdb..sp_help_job". Is this something that my script is calling? I just wonder why banch of those processes are hanging there with the same properties...

Any inputs are appreciated.

TIA,
kerroppa