Trying to use sp_stop_job to stop a simple job that inserts data on a table when 100 rows were inserted I got confused to see the table end up with more than 100 rows every time. Under the Remarks section of the sp_stop_job doc it says:

sp_stop_job sends a stop signal to the database. Some processes can be stopped immediately and some must reach a stable point (or an entry point to the code path) before they can stop.

I don't understand what it means to reach a stable point (or an entry point to the code path) and I believe the job keeps inserting the data on the table because it is still reaching that stable point.

The code:

CREATE TABLE [dbo].[myStopJobTable](
    [id] [int] NULL,
    [nome] [varchar](50) NULL

I created a job with a step to execute the following code:

WHILE (1=1)

    INSERT INTO MyLab.dbo.myStopJobTable
    VALUES (1, 'Some Name');

    IF ((SELECT COUNT(*) FROM myStopJobTable) = 100)
        EXEC msdb.dbo.sp_stop_job N'JobToBeStopped';


From that sample code I expected the job to stop before the row 101 is inserted, but it doesn't. By adding a BREAK inside the IF block the process is stopped before the insertion of row 101 happens, but in that case it's not the sp_stop_job working.

I don't want a fix for the job, what I seek is to understand that stable point (or an entry point to the code path). What does it mean?

1 Answer 1


The job is not waiting to "reach a stable point". The key point is:

sp_stop_job sends a stop signal to the database.

sp_stop_job sends the stop request SQL Server Agent but does not wait for it to complete. The T-SQL script continues while SQL Server Agent processes the stop request asynchronously. The additional rows inserted after sp_stop_job was executed reflect the time between receipt of the stop request until SQL Server Agent got around to cancelling it. This typically takes a few milliseconds or more depending on overall activity.

The note about "reach a stable point" in the context of a T-SQL job step is the same as any query cancel request. It includes the time the database engine needs to release resources and rollback the transaction. That time is insignificant with the singleton insert in your example but would be more noticeable had the cancelled script done more work, like inserted millions of rows in a single transaction.

  • Thanks, Dan Guzman. I didn't expect the SQL Server Agent to take that long (time enough to insert more rows on the table) to respond to the stop signal. I noticed the problem on an instance on my desktop, so SQL Server wasn't busy to justify the delay.
    – Ronaldo
    Commented Apr 12, 2022 at 12:29
  • 1
    @Ronaldo, the insert script is running in a tight loop so the number of rows inserted before the script actually stops can be significant if you have a fast machine and storage. On my workstation (high-end processor and SSD storage), over 200 rows were inserted between the sp_stop_job and the attention signal to cancel the script. I saw no additional rows after adding WAITFOR DELAY '00:00:00.100'; after sp_stop_job.
    – Dan Guzman
    Commented Apr 12, 2022 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.