June 23, 2014 at 12:36 pm
Business case: Somewhat similarly to The 2011 post A Saturday QOTD - If you get it right and live in Nashville, TN a few Happy Hour Brews are on ME!, I have a need to have a job run from another job without raising an error if it's already running or queued.
After some research, I've come up with the following IF EXISTS; if anyone would like to review it and comment on improvements (or use it), I would appreciate it.
First, a test agent job that waits 2 seconds.
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'TEST WAIT 2 SECONDS',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @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'wait 2 seconds',
@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:02''',
@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
Now the actual IF EXISTS, implemented in quadruplicate with timed delays as a test harness:
-- Check time
SELECT CURRENT_TIMESTAMP
-- First we run it
IF EXISTS (
SELECT sj.job_id
FROM msdb.dbo.sysjobs sj
LEFT OUTER JOIN
(
SELECT sa.job_id
FROM msdb.dbo.sysjobactivity sa
-- orphaned/ancient sysjobactivity row cleanup syssessions join from http://www.sqlservercentral.com/Forums/Topic1452450-146-3.aspx
-- sysajobactivity join MUST use run_requested_date instead of start_execution date to prevent 'Msg 22022, Level 16, State 1, Line nnn SQLServerAgent Error: Request to run job jjj (from User ddd\uuu) refused because the job already has a pending request from User ddd\uuu.'
INNER JOIN
(
SELECT MAX(session_ID) AS MaxSession_ID
FROM msdb.dbo.syssessions
) currentSessionIErestart
ON currentSessionIErestart.MaxSession_ID = sa.session_id
WHERE sa.job_history_id IS null AND sa.run_requested_date is NOT NULL
) dvIsRunningNow
ON dvIsRunningNow.job_id = sj.job_id
WHERE sj.enabled = 1
AND sj.name = 'TEST WAIT 2 SECONDS'
AND dvIsRunningNow.job_id IS NULL
)
EXEC msdb.dbo.sp_start_job 'TEST WAIT 2 SECONDS'
-- Now the first test, another run very quickly afterwards to check for the "job already has a pending request" issue (sa.start_execution_date null)
IF EXISTS (
SELECT sj.job_id
FROM msdb.dbo.sysjobs sj
LEFT OUTER JOIN
(
SELECT sa.job_id
FROM msdb.dbo.sysjobactivity sa
-- orphaned/ancient sysjobactivity row cleanup syssessions join from http://www.sqlservercentral.com/Forums/Topic1452450-146-3.aspx
-- sysajobactivity join MUST use run_requested_date instead of start_execution date to prevent 'Msg 22022, Level 16, State 1, Line nnn SQLServerAgent Error: Request to run job jjj (from User ddd\uuu) refused because the job already has a pending request from User ddd\uuu.'
INNER JOIN
(
SELECT MAX(session_ID) AS MaxSession_ID
FROM msdb.dbo.syssessions
) currentSessionIErestart
ON currentSessionIErestart.MaxSession_ID = sa.session_id
WHERE sa.job_history_id IS null AND sa.run_requested_date is NOT NULL
) dvIsRunningNow
ON dvIsRunningNow.job_id = sj.job_id
WHERE sj.enabled = 1
AND sj.name = 'TEST WAIT 2 SECONDS'
AND dvIsRunningNow.job_id IS NULL
)
EXEC msdb.dbo.sp_start_job 'TEST WAIT 2 SECONDS'
-- And now a SELECT to see what weirdness may exist if we get an error
SELECT CURRENT_TIMESTAMP, sj.job_id, sa.*
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobactivity sa
ON sa.job_id = sj.job_id
WHERE sj.enabled = 1
AND sa.job_history_id IS null
AND sj.name = 'TEST WAIT 2 SECONDS'
-- Now the second test, another run after a second to check for the "job already running" issue (sa.start_execution_date not null)
WAITFOR DELAY '00:00:01'
IF EXISTS (
SELECT sj.job_id
FROM msdb.dbo.sysjobs sj
LEFT OUTER JOIN
(
SELECT sa.job_id
FROM msdb.dbo.sysjobactivity sa
-- orphaned/ancient sysjobactivity row cleanup syssessions join from http://www.sqlservercentral.com/Forums/Topic1452450-146-3.aspx
-- sysajobactivity join MUST use run_requested_date instead of start_execution date to prevent 'Msg 22022, Level 16, State 1, Line nnn SQLServerAgent Error: Request to run job jjj (from User ddd\uuu) refused because the job already has a pending request from User ddd\uuu.'
INNER JOIN
(
SELECT MAX(session_ID) AS MaxSession_ID
FROM msdb.dbo.syssessions
) currentSessionIErestart
ON currentSessionIErestart.MaxSession_ID = sa.session_id
WHERE sa.job_history_id IS null AND sa.run_requested_date is NOT NULL
) dvIsRunningNow
ON dvIsRunningNow.job_id = sj.job_id
WHERE sj.enabled = 1
AND sj.name = 'TEST WAIT 2 SECONDS'
AND dvIsRunningNow.job_id IS NULL
)
EXEC msdb.dbo.sp_start_job 'TEST WAIT 2 SECONDS'
-- And now a SELECT to see what weirdness may exist if we get an error
SELECT CURRENT_TIMESTAMP, sj.job_id, sa.*
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobactivity sa
ON sa.job_id = sj.job_id
WHERE sj.enabled = 1
AND sa.job_history_id IS null
AND sj.name = 'TEST WAIT 2 SECONDS'
-- Now the third test, another run after just over another second to make sure it starts again.
-- NOTE: A delay of even '00:00:01.8' failed to start the job most of the time.
WAITFOR DELAY '00:00:02.3'
IF EXISTS (
SELECT sj.job_id
FROM msdb.dbo.sysjobs sj
LEFT OUTER JOIN
(
SELECT sa.job_id
FROM msdb.dbo.sysjobactivity sa
-- orphaned/ancient sysjobactivity row cleanup syssessions join from http://www.sqlservercentral.com/Forums/Topic1452450-146-3.aspx
-- sysajobactivity join MUST use run_requested_date instead of start_execution date to prevent 'Msg 22022, Level 16, State 1, Line nnn SQLServerAgent Error: Request to run job jjj (from User ddd\uuu) refused because the job already has a pending request from User ddd\uuu.'
INNER JOIN
(
SELECT MAX(session_ID) AS MaxSession_ID
FROM msdb.dbo.syssessions
) currentSessionIErestart
ON currentSessionIErestart.MaxSession_ID = sa.session_id
WHERE sa.job_history_id IS null AND sa.run_requested_date is NOT NULL
) dvIsRunningNow
ON dvIsRunningNow.job_id = sj.job_id
WHERE sj.enabled = 1
AND sj.name = 'TEST WAIT 2 SECONDS'
AND dvIsRunningNow.job_id IS NULL
)
EXEC msdb.dbo.sp_start_job 'TEST WAIT 2 SECONDS'
-- And now a SELECT to see what weirdness may exist if we get an error
SELECT CURRENT_TIMESTAMP, sj.job_id, sa.*
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobactivity sa
ON sa.job_id = sj.job_id
WHERE sj.enabled = 1
AND sa.job_history_id IS null
AND sj.name = 'TEST WAIT 2 SECONDS'
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply