patternsqlModerate
Close button action when manually running a job
Viewed 0 times
closeactionmanuallyrunningbuttonwhenjob
Problem
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).
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).
Solution
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:
To prove the "close" button does not actually stop a running job, I've created a test job:
The job takes 15 seconds to complete, by virtue of the
-
Open a new query window, and add the following T-SQL to it:
-
Run the job via the SSMS user interface, and immediately hit the "Close" button.
-
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
╔════════════╦══════════════════════════════════════╦══════════╦═════════════════════════╦══════════════════════╦═════════════╦═════════════════════════╦═══════════════════════╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════╦═════════════════════════════════════════════════════════════
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 = '';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'', @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:
GOThe job takes 15 seconds to complete, by virtue of the
WAITFOR DELAY statement. It does nothing else.-
Open a new query window, and add the following T-SQL to it:
EXEC msdb.dbo.sp_help_jobactivity @job_name = 'TestJob';-
Run the job via the SSMS user interface, and immediately hit the "Close" button.
-
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:╔════════════╦══════════════════════════════════════╦══════════╦═════════════════════════╦══════════════════════╦═════════════╦═════════════════════════╦═══════════════════════╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════╦═════════════════════════════════════════════════════════════
Code Snippets
EXEC msdb.dbo.sp_help_jobactivity @job_name = '<job name here>';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:
GOEXEC msdb.dbo.sp_help_jobactivity @job_name = 'TestJob';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'Context
StackExchange Database Administrators Q#196723, answer score: 14
Revisions (0)
No revisions yet.