Using a stop event in CMDEXEC subsystem

One of the flags that can be specified for a Sql Agent jobstep is 64 (stop event flag). See the article (in the @flags section).

This flag is only allowed for CMDEXEC subsystem jobstep.

The purpose of the flag is to allow the user app run as part of the jobstep to clean up in case the job is cancelled or Sql Agent is shutting down. The way this is implemented is using a named Windows event that is accessible to children processes of Sql Agent. Since the jobstep for CMDEXEC runs as a child process, any application started in this jobstep can wait on the event and execute the required cleanup.

Here is how to use it:

1) Create the job and jobstep

When creating a CMDEXEC jobstep, specify the @flags parameter to include the value 64 (this value can be AND-ed with other options as desired). See the MSDN page above on how to create a jobstep using sp_add_jobstep. There is no UI option for setting this flag in SSMS, but you can use Script Action As to generate the script, then add the @flags parameter.

2 ) Wait on the named event

The user application that is called in the jobstep can now wait on the event. The event name has the following pattern:


where %s is the @job_id for the owner job in the same string format as returned by sp_add_job

and %lu is the step_id as unsigned integer.

For example:

@job_id = 0x6F8D3CF3BD53F64F8A0A92319835E01B

@step_id = 1 (first step of the job)

then the event name will be 0x6F8D3CF3BD53F64F8A0A92319835E01B_1_STOP

3) Act on the event

If the running job is canceled or Sql Agent is shutting down, the named event above will be triggered and the user can take the cleanup actions.

The event will time out after a while and the jobstep (and the user application) will be killed if they are still running.

The formula for calculating the timeout is:

StopEventJobShutdownTimeout = max(1, JobShutdownTimeout - 5); -- in seconds.

where JobShutdownTimeout is read from the registry (see

By default JobShutdownTimeout is 15 seconds so the default for StopEventJobShutdownTimeout is 10 seconds.

Comments (0)

Skip to main content