Member Avatar for sqlchopper

Current app runs a stored procedure which runs 16 nested procedures and takes from 3 to 10 min depending on how must data needs download from the as400. The problem is that there are some other web apps on the web serverr that use old com objects that have memory leaks and/or does not release memory. My apps runs without and problems. However when when enough memory is chewed up we get a memory error. Network services then stops IIS and restarts.
If my app is running the stored procedure it hang when they reset IIS. Is there a way to run the stored procedure and continue on with out waiting on the return code. I would change the stored procedure to update a flag somewhere that I could checkevery x min to see if it has completed.

  • 3 Contributors
  • 6 Replies
  • 354 Views
  • 6 Months Discussion Span
  • Latest Post Latest Post by sqlchopper

Dani AI

Generated

Good solutions are already in this thread, but a few practical, reliable options and clarifications will help make the behavior deterministic and production-safe.

Create and start a SQL Agent job (recommended)

  • Put the long work in a SQL Agent job step and invoke it from the web app. Calling msdb.dbo.sp_start_job returns immediately; the Agent runs the job outside the IIS process so IIS restarts do not kill the work (SQL Agent must be running). Example:
    -- starts a pre-created job and returns immediately
EXEC msdb.dbo.sp_start_job @job_name = 'MyLongProcJob';

    Have the job update a status/control table and write error/log rows so the web UI can poll status.

Service Broker (robust, scalable)

  • For a higher‑reliability, queue-based approach, enqueue a message to a Service Broker queue and let an activation proc process it. This survives client disconnects, supports retries, and is ideal for many concurrent requests, but it requires more setup.

Other approaches (use with caution)

  • Running a batch via sqlcmd or a scheduled Windows Task is simple and keeps work outside IIS (this is what was pointing toward). Launching external processes directly from IIS is brittle and has security/permission implications. xp_cmdshell is not recommended for security reasons.

Important clarifications and tips

  • Temp table scope: a local temp table lives until the scope that created it ends — if the parent creates #temp it persists for nested calls; a temp created inside a child proc is dropped when that child returns. Splitting work into smaller units reduces tempdb pressure.
  • Transactions: keep transactions short. Nested transactions in SQL Server do not fully commit until the outermost commit. Use chunked commits or snapshot isolation where appropriate to avoid long locks and excessive tempdb use.
  • Security and reliability: ensure SQL Agent is running, give the app only the minimal rights needed to start jobs (or use a signed/wrapper proc or controlled impersonation), and add robust logging and error handling in the job.

Good work by splitting the SPs and using a control flag; replacing the poll with sp_start_job or Service Broker will remove the IIS-dependency while keeping your current status-check pattern.

Recommended Answers

All 6 Replies

Member Avatar for Estuardo

G'd evening sqlchopper!
If that happens often you should consider to run your sproc(s) within transactions. It will not just avoid your application hang, but also and more important it will keep the data integrity.

Member Avatar for Kate Albany

sqlchopper,

If this SP can be run by something other than the web app, then why not create a batch file which uses 'sqlplus' to connect to the DB to run the SP. This can be scheduled to run automatacally or on demand. That way you can start/stop IIS - hell even uninstall it - and it will not affect your SP.

If you do need to run it from the web app then there is no way to stop it being killed when IIS is shutdown. Depending on what your using to connect to the DB ... OLEDB/ADO etc ... you can execute using the 'asynchronous' flag, this allows you to call into the DB and have it return control to the app immediatly, like you wanted. However if the web app dies (IIS shutdown) your connection to the DB will die along with it, and the DB will terminate your session and everything it was doing, I.E. running your SP. and will rollback any un-committed transactions.

Running the SP out of IIS's control (batch script) is the only way.

I have just realised that 'sqlplus' is for the Oracle DB and this thread is for MS SQL DB, sorry force of habbit, I am more familiar with Oracle. I'm not sure if there is something similar to sqlplus you can use with MS SQL.

Kate

Member Avatar for sqlchopper

sqlchopper,

If this SP can be run by something other than the web app, then why not create a batch file which uses 'sqlplus' to connect to the DB to run the SP. This can be scheduled to run automatacally or on demand. That way you can start/stop IIS - hell even uninstall it - and it will not affect your SP.

If you do need to run it from the web app then there is no way to stop it being killed when IIS is shutdown. Depending on what your using to connect to the DB ... OLEDB/ADO etc ... you can execute using the 'asynchronous' flag, this allows you to call into the DB and have it return control to the app immediatly, like you wanted. However if the web app dies (IIS shutdown) your connection to the DB will die along with it, and the DB will terminate your session and everything it was doing, I.E. running your SP. and will rollback any un-committed transactions.

Running the SP out of IIS's control (batch script) is the only way.

I have just realised that 'sqlplus' is for the Oracle DB and this thread is for MS SQL DB, sorry force of habbit, I am more familiar with Oracle. I'm not sure if there is something similar to sqlplus you can use with MS SQL.

Kate

thanks, I'll look and see. What I did in the mean time was chopped the stored procedures up and run each one seperatly. it reduced the resources and time to less that 5 min. What I did understand that is all the temp file files created are kept until the procedure is finished. What I did not realize is that when you nest sp's they are all kept untill the top most procedure is done.

Member Avatar for sqlchopper

G'd evening sqlchopper!
If that happens often you should consider to run your sproc(s) within transactions. It will not just avoid your application hang, but also and more important it will keep the data integrity.

how do I do that?

Member Avatar for Estuardo

G'd evening Kate & sqlchopper!

From BOL (SQL Books On Line)
Transactions
A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

Also you can donwload the BOL from .
Good luck
Estuardo

Member Avatar for sqlchopper

Thanks, just a quick note. After several failed attemps I created a sp that updates a control file record which takes only a second or less. I then created a 3 sp jobs that run ever 5 min and check to sse if falg is turned on. if it is it then executes the sp I need run. since I have 20+ plants that could request it I found the 3 job was sufficent to noy hold anyone up.
I have since discarded that and now run each sp (23 of then) in the web app individuly and the total run time is 5 min or less.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.