Run long-running sproc (that doesn't need to return) from ASP.NET page

Question

I would like to know how you would run a stored procedure from a page and just "let it finish" even if the page is closed. It doesn't need to return any data.

Answer 1

Prepare the command first, then queue it in the threadpool. Just make sure the thread does not depend on any HTTP Context or any other http intrinsic object. If your request finishes before the thread; the context might be gone.

Answer 2

See this article.

Answer 3

A database-centric option would be:

• Create a table that will contain a list (or queue) of long-running jobs to be performed.
• Have the application add an entry to the queue if, when, and as desired. That's all it does; once logged and entered, no web session or state data need be maintained.
• Have a SQL Agent job configured to check every 1, 2, 5, whatever minutes to see if there are any jobs to run.
• If there are as-yet unstarted items, mark the most recent one as started, and start it.
• When it's completed, mark it as completed, or just delete it
• Check if there are any other items to run. If there are, repeat; if not, exit the job.

Depending on capacity, you could have several (differently named) copies of this job running, concurrently processing items from the list.

(I've used this method for very long-running methods. It's more an admin-type trick, but it may be appropriate for your situation.)

Answer 4

See Asynchronous procedure execution. This is the only method that guarantees the execution even if the ASP process crashes. It also self tuning and can handle spikes of load, requests are queued up and processed as resources become available.

The gist of the solution is leveraging the SQL Server Activation concept, which allows you to run a stored procedure in a background thread in SQL Server without a client connection.

Solutions based on SqlClient asynch methods or on CLR thread pool are unreliable, the calls are lost as the ASP process is recycled, and besides they build up in-memory queues of requests that actually trigger a process recycle due to memory consumption.

Solutions based on tables and Agent jobs are better, as they are reliable, but they lack the self tuning of Activation based solutions.