1

Waiting for a job to complete

 3 years ago
source link: https://thelonedba.wordpress.com/2020/07/21/waiting-for-a-job-to-complete/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Waiting for a job to complete

You know how it is. You need to wait for a job to complete before moving onto the next one… And, just sometimes, you need to do the same with SQL Server.

The msdb system database contains various tables for dealing with the SQL Agent and the job scheduler. The ones of particular interest for this problem are:

syssessions The syssessions table contains a list of the SQLAgent sessions. Unfortunately, there’s no absolute guarantee that the session_id is in ascending order, so we need to pull the top value when sorted by agent_start_date in descending order. sysjobs The sysjobs table contains information about each scheduled (and unscheduled) job. We’re only interested in the job_id and job_name fields at the moment. sysjobactivity The sysjobactivity table contains data about what jobs are running in the current session and, indeed, can be used as a snapshot of what jobs were running when a previous SQLAgent instance was ended.

Final code

Here follows a quick & dirty query to watch for one job to finish, and then to start the next job:

USE msdb;
DECLARE @JobToWaitFor sysname = 'First Job';
DECLARE @JobToLaunchNext sysname = 'Second Job';
WHILE EXISTS
(
SELECT j.name,
ja.*
FROM sysjobs j
INNER JOIN sysjobactivity ja
ON ja.job_id = j.job_id
WHERE ja.session_id =
(
-- can't use max session_id, as not guaranteed to be the most recent agent start date
SELECT TOP 1 session_id FROM syssessions ORDER BY agent_start_date DESC
)
AND ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL
AND j.name = @JobToWaitFor
)
BEGIN
-- delay loop here
WAITFOR DELAY '00:00:10'; -- tweak this as appropriate to your job length, or desired delay between job finish/start
END;
EXEC sp_start_job @job_name = @JobToLaunchNext;

Yes, there are more elegant / succinct ways to do this – you could achieve similar results by using sp_help_job for example – but using script snippets like this can allow a more complex job workflow than merely “start this job, run this script” – in the past, I’ve written similar code to launch several jobs to run in parallel and then wait for them all to finish before moving onto the next step.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK