We have dozens of SQL jobs that write SQL data to files and by the time we go live with VisualCron will have hundreds. Some of them use stored procedures while others use SQL commands/text. Once in a while we need to stop one of these jobs because it is long-running or something else is happening where we do not want the job to complete.

If we try to stop the job using the Stop Job button (or right-click menu or Task Manager tool or Running Jobs tool), either nothing happens for several minutes or the server crashes.

We have tried different approaches such as stopping the SQL task instead of the whole job, pausing the job and task, resetting the job, deactivating the job, etc. Most of the time the server doesn't crash, but it happens enough to make us concerned.

What is a clean way to stop a SQL task/job? Once you click Stop Job, should that button be greyed out (is there any indication that the job is attempting to stop)?

Update: After more testing, we are finding that the server crashes mainly when we attempt to stop the same job more than once. That said, late on the day Friday we clicked Stop Job just once, let the job stop (it will eventually stop if we wait up to 4 minutes or so we are finding), but then this morning no one could connect to the server. While the Services console in Windows Server showed the server still running, the only way we could get connected to it was to restart the service. The server was down all weekend and no none knew it.

Anyone else running SQL jobs? Do you have a workaround?

My guess is that you have transactions that need to be rolled back when the connection/session is interrupted. A rollback in sql server can be slow at it never uses parallell processing (to my understanding). If it is in a rollback process, just let it finish. You could also chunck up the job into smaller transactions to make it a bit more manageable for sql server. When it comes to what VisualCron does when you stop a job, I don't know.