Sql-server – Close button action when manually running a job

sql serversql-server-2016sql-server-agent

I started a job from SSMS on my laptop using Right Click > Start Job at Step… When I was ready to be done for the day the job was still running so I selected Close thinking it was operating on the server and would continue on the server.

Today I couldn't find the job for that run so I'm second guessing how that popup operates (screen shot of popup in question below).

What happens when the Close button is clicked? If the job is terminated how exactly is it terminated (e.g. something trappable; the job in question is an Operation System type running under a proxy account).

popup containing Close button in question

Best Answer

The "close" button simply closes the dialog; it does not stop the running job.

You can use the following stored procedure to see the current runtime state of a given job:

EXEC msdb.dbo.sp_help_jobactivity @job_name = '<job name here>';

To prove the "close" button does not actually stop a running job, I've created a test job:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TestJob', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=2, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', 
        @notify_page_operator_name=N'<Operator Name>', @job_id = @jobId OUTPUT;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'WAITFOR DELAY ''00:00:15'';', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The job takes 15 seconds to complete, by virtue of the WAITFOR DELAY statement. It does nothing else.

  1. Open a new query window, and add the following T-SQL to it:

    EXEC msdb.dbo.sp_help_jobactivity @job_name = 'TestJob';

  2. Run the job via the SSMS user interface, and immediately hit the "Close" button.

  3. Run the job activity query in step 1 by hitting the F5 key or clicking the "Execute" button.

The status of the job will be shown, something like:

╔════════════╦══════════════════════════════════════╦══════════╦═════════════════════════╦══════════════════════╦═════════════╦═════════════════════════╦═══════════════════════╦═════════════════════════╦═════════════════════╦═════════════════════════╦════════════════╦═════════╦════════════╦═════════════════════╦═════════════════════╦═══════════════════╗
║ session_id ║                job_id                ║ job_name ║   run_requested_date    ║ run_requested_source ║ queued_date ║  start_execution_date   ║ last_executed_step_id ║ last_executed_step_date ║ stop_execution_date ║ next_scheduled_run_date ║ job_history_id ║ message ║ run_status ║ operator_id_emailed ║ operator_id_netsent ║ operator_id_paged ║
╠════════════╬══════════════════════════════════════╬══════════╬═════════════════════════╬══════════════════════╬═════════════╬═════════════════════════╬═══════════════════════╬═════════════════════════╬═════════════════════╬═════════════════════════╬════════════════╬═════════╬════════════╬═════════════════════╬═════════════════════╬═══════════════════╣
║      34156 ║ 2BDBF2EA-7E23-447B-A070-97274FFD7EF5 ║ TestJob  ║ 2018-01-31 08:51:12.000 ║                    4 ║ NULL        ║ 2018-01-31 08:51:13.000 ║ NULL                  ║ NULL                    ║ NULL                ║ NULL                    ║ NULL           ║ NULL    ║ NULL       ║ NULL                ║ NULL                ║ NULL              ║
╚════════════╩══════════════════════════════════════╩══════════╩═════════════════════════╩══════════════════════╩═════════════╩═════════════════════════╩═══════════════════════╩═════════════════════════╩═════════════════════╩═════════════════════════╩════════════════╩═════════╩════════════╩═════════════════════╩═════════════════════╩═══════════════════╝

In the above output, the stop_execution_date column is NULL, indicating the job is still running. If you wait 15 seconds for the job to complete, and re-run step 1, you'll see the output is changed to reflect the status of the job, as in:

╔════════════╦══════════════════════════════════════╦══════════╦═════════════════════════╦══════════════════════╦═════════════╦═════════════════════════╦═══════════════════════╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════╦═══════════════════════════════════════════════════════════════════════════════════════════════╦════════════╦═════════════════════╦═════════════════════╦═══════════════════╗
║ session_id ║                job_id                ║ job_name ║   run_requested_date    ║ run_requested_source ║ queued_date ║  start_execution_date   ║ last_executed_step_id ║ last_executed_step_date ║   stop_execution_date   ║ next_scheduled_run_date ║ job_history_id ║                                            message                                            ║ run_status ║ operator_id_emailed ║ operator_id_netsent ║ operator_id_paged ║
╠════════════╬══════════════════════════════════════╬══════════╬═════════════════════════╬══════════════════════╬═════════════╬═════════════════════════╬═══════════════════════╬═════════════════════════╬═════════════════════════╬═════════════════════════╬════════════════╬═══════════════════════════════════════════════════════════════════════════════════════════════╬════════════╬═════════════════════╬═════════════════════╬═══════════════════╣
║      34156 ║ 2BDBF2EA-7E23-447B-A070-97274FFD7EF5 ║ TestJob  ║ 2018-01-31 08:51:12.000 ║                    4 ║ NULL        ║ 2018-01-31 08:51:13.000 ║                     1 ║ 2018-01-31 08:51:13.000 ║ 2018-01-31 08:51:28.000 ║ NULL                    ║        3514780 ║ The job succeeded.  The Job was invoked by User me.  The last step to run was step 1 (Step1). ║          1 ║                   0 ║                   0 ║                 0 ║
╚════════════╩══════════════════════════════════════╩══════════╩═════════════════════════╩══════════════════════╩═════════════╩═════════════════════════╩═══════════════════════╩═════════════════════════╩═════════════════════════╩═════════════════════════╩════════════════╩═══════════════════════════════════════════════════════════════════════════════════════════════╩════════════╩═════════════════════╩═════════════════════╩═══════════════════╝

The dbo.sp_help_jobactivity stored procedure references the dbo.sysjobactivity table. Microsoft docs for that table shows they use the following construct to determine the current status of a job:

CASE
    WHEN sja.start_execution_date IS NULL THEN 'Not running'
    WHEN sja.start_execution_date IS NOT NULL 
        AND sja.stop_execution_date IS NULL THEN 'Running'
    WHEN sja.start_execution_date IS NOT NULL 
        AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus'
Related Question