Spread the love

SQL “Watchdog” loop to start and monitor SQL Agent Jobs

This runs an SQL “Watchdog” loop to start and monitor SQL Agent Jobs. There are just a few configurable items that you can adjust to suit your needs:

The “Wait Delay”, which “sleeps” for the specified amount of time before checking the status of the jobs again.
The “Timeout” in minutes, which tells the watchdog how many minutes a job is allowed to run before being stoped by the watchdog.
The name of the job(s) you want the watchdog to start and monitor. One insert statement per job.

That’s it! Enjoy!

Code:

/****************************************************************
--This SQL will take a list of SQL Agent jobs (names must match),
--start them so they're all running together, and then
--monitor them, not quitting until all jobs have completed.
--
--In essence, it's an SQL "watchdog" loop to start and monitor SQL Agent Jobs
--
--Code from http://cc.davelozinski.com
--
****************************************************************/
SET NOCOUNT ON 

-------- BEGIN ITEMS THAT NEED TO BE CONFIGURED --------

--The amount of time to wait before checking again 
--to see if the jobs are still running.
--Should be in hh:mm:ss format. 
DECLARE @WaitDelay VARCHAR(8) = '00:00:20'

--Job timeout. Eg, if the jobs are running longer than this, kill them.
DECLARE @TimeoutMinutes INT = 240

DECLARE @JobsToRunTable TABLE
(
	JobName NVARCHAR(128) NOT NULL,
	JobID UNIQUEIDENTIFIER NULL,
	Running INT NULL
)

--Insert the names of the SQL jobs here. Last two values should always be NULL at this point.
--Names need to match exactly, so best to copy/paste from the SQL Server Agent job name.
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfFirstSQLAgentJobToRun',NULL,NULL)
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfSecondSQLAgentJobToRun',NULL,NULL)
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfXSQLAgentJobToRun',NULL,NULL)

-------- NOTHING FROM HERE DOWN SHOULD NEED TO BE CONFIGURED --------

DECLARE @ExecutionStatusTable TABLE
(
	JobID UNIQUEIDENTIFIER PRIMARY KEY, -- Job ID which will be a guid
	LastRunDate INT, LastRunTime INT, -- Last run date and time
	NextRunDate INT, NextRunTime INT, -- Next run date and time
	NextRunScheduleID INT, -- an internal schedule id
	RequestedToRun INT, RequestSource INT, RequestSourceID VARCHAR(128),
	Running INT,	-- 0 or 1, 1 means the job is executing
	CurrentStep INT, -- which step is running
	CurrentRetryAttempt INT, -- retry attempt
	JobState INT -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread,
                     -- 3 = Between Retries, 4 = Idle, 5 = Suspended, 
                     -- 6 = WaitingForStepToFinish, 7 = PerformingCompletionActions
)

DECLARE @JobNameToRun NVARCHAR(128) = NULL
DECLARE @IsJobRunning BIT = 1
DECLARE @AreJobsRunning BIT = 1
DECLARE @job_owner sysname = SUSER_SNAME()
DECLARE @JobID UNIQUEIDENTIFIER = null
DECLARE @StartDateTime DATETIME = GETDATE()
DECLARE @CurrentDateTime DATETIME = null
DECLARE @ExecutionStatus INT = 0
DECLARE @MaxTimeExceeded BIT = 0

--Loop through and start every job
DECLARE dbCursor CURSOR FOR SELECT JobName FROM @JobsToRunTable
OPEN dbCursor FETCH NEXT FROM dbCursor INTO @JobNameToRun
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC [msdb].[dbo].sp_start_job @JobNameToRun
	FETCH NEXT FROM dbCursor INTO @JobNameToRun
END
CLOSE dbCursor
DEALLOCATE dbCursor

print '*****************************************************************'
print 'Jobs started. ' + CAST(@StartDateTime as varchar)
print '*****************************************************************'

--Debug (if needed)
--SELECT * FROM @JobsToRunTable

WHILE 1=1 AND @AreJobsRunning = 1
BEGIN

	--This has to be first with the delay to make sure the jobs
	--have time to actually start up and are recognized as 'running'
	WAITFOR DELAY @WaitDelay 

	--Reset for each loop iteration
	SET @AreJobsRunning = 0

	--Get the currently executing jobs by our user name
	INSERT INTO @ExecutionStatusTable
	EXECUTE [master].[dbo].xp_sqlagent_enum_jobs 1, @job_owner

	--Debug (if needed)
	--SELECT 'ExecutionStatusTable', * FROM @ExecutionStatusTable

	--select every job to see if it's running
	DECLARE dbCursor CURSOR FOR 
		SELECT x.[Running], x.[JobID], sj.name 
		FROM @ExecutionStatusTable x 
		INNER JOIN [msdb].[dbo].sysjobs sj ON sj.job_id = x.JobID
		INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName
	OPEN dbCursor FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun

	--Debug (if needed)
	--SELECT x.[Running], x.[JobID], sj.name 
	--	FROM @ExecutionStatusTable x 
	--	INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.JobID
	--	INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		--bitwise operation to see if the loop should continue
		SET @AreJobsRunning = @AreJobsRunning | @IsJobRunning

		UPDATE @JobsToRunTable
		SET Running = @IsJobRunning, JobID = @JobID
		WHERE JobName = @JobNameToRun

		--Debug (if needed)
		--SELECT 'JobsToRun', * FROM @JobsToRunTable
		
		SET @CurrentDateTime=GETDATE()

		IF @IsJobRunning = 1
		BEGIN -- Job is running or finishing (not idle)

			IF DATEDIFF(mi, @StartDateTime, @CurrentDateTime) > @TimeoutMinutes
			BEGIN     
				print '*****************************************************************'
				print @JobNameToRun + ' exceeded timeout limit of ' + @TimeoutMinutes + ' minutes. Stopping.'
				--Stop the job
				EXEC [msdb].[dbo].sp_stop_job @job_name = @JobNameToRun
			END
			ELSE
			BEGIN
				print @JobNameToRun + ' running for ' + CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + ' minute(s).'
			END
		END

		IF @IsJobRunning = 0 
		BEGIN
			--Job isn't running
			print '*****************************************************************'
			print @JobNameToRun + ' completed or did not run. ' + CAST(@CurrentDateTime as VARCHAR)
		END

		FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun

	END -- WHILE @@FETCH_STATUS = 0
	CLOSE dbCursor
	DEALLOCATE dbCursor

	--Clear out the table for the next loop iteration
	DELETE FROM @ExecutionStatusTable

	print '*****************************************************************'

END -- WHILE 1=1 AND @AreJobsRunning = 1

SET @CurrentDateTime = GETDATE()
print 'Finished at ' + CAST(@CurrentDateTime as varchar)
print CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + ' minutes total run time.'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

/****************************************************************

--This SQL will take a list of SQL Agent jobs (names must match),

--start them so they're all running together, and then

--monitor them, not quitting until all jobs have completed.

--In essence, it's an SQL "watchdog" loop to start and monitor SQL Agent Jobs

--Code from http://cc.davelozinski.com

****************************************************************/

SET NOCOUNT ON

-------- BEGIN ITEMS THAT NEED TO BE CONFIGURED --------

--The amount of time to wait before checking again

--to see if the jobs are still running.

--Should be in hh:mm:ss format.

DECLARE @WaitDelay VARCHAR(8) = '00:00:20'

--Job timeout. Eg, if the jobs are running longer than this, kill them.

DECLARE @TimeoutMinutes INT = 240

DECLARE @JobsToRunTable TABLE

(

JobName NVARCHAR(128) NOT NULL,

JobID UNIQUEIDENTIFIER NULL,

Running INT NULL

)

--Insert the names of the SQL jobs here. Last two values should always be NULL at this point.

--Names need to match exactly, so best to copy/paste from the SQL Server Agent job name.

INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfFirstSQLAgentJobToRun',NULL,NULL)

INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfSecondSQLAgentJobToRun',NULL,NULL)

INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfXSQLAgentJobToRun',NULL,NULL)

-------- NOTHING FROM HERE DOWN SHOULD NEED TO BE CONFIGURED --------

DECLARE @ExecutionStatusTable TABLE

(

JobID UNIQUEIDENTIFIER PRIMARY KEY, -- Job ID which will be a guid

LastRunDate INT, LastRunTime INT, -- Last run date and time

NextRunDate INT, NextRunTime INT, -- Next run date and time

NextRunScheduleID INT, -- an internal schedule id

RequestedToRun INT, RequestSource INT, RequestSourceID VARCHAR(128),

Running INT, -- 0 or 1, 1 means the job is executing

CurrentStep INT, -- which step is running

CurrentRetryAttempt INT, -- retry attempt

JobState INT -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread,

-- 3 = Between Retries, 4 = Idle, 5 = Suspended,

-- 6 = WaitingForStepToFinish, 7 = PerformingCompletionActions

)

DECLARE @JobNameToRun NVARCHAR(128) = NULL

DECLARE @IsJobRunning BIT = 1

DECLARE @AreJobsRunning BIT = 1

DECLARE @job_owner sysname = SUSER_SNAME()

DECLARE @JobID UNIQUEIDENTIFIER = null

DECLARE @StartDateTime DATETIME = GETDATE()

DECLARE @CurrentDateTime DATETIME = null

DECLARE @ExecutionStatus INT = 0

DECLARE @MaxTimeExceeded BIT = 0

--Loop through and start every job

DECLARE dbCursor CURSOR FOR SELECT JobName FROM @JobsToRunTable

OPEN dbCursor FETCH NEXT FROM dbCursor INTO @JobNameToRun

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC [msdb].[dbo].sp_start_job @JobNameToRun

FETCH NEXT FROM dbCursor INTO @JobNameToRun

END

CLOSE dbCursor

DEALLOCATE dbCursor

print '*****************************************************************'

print 'Jobs started. ' + CAST(@StartDateTime as varchar)

print '*****************************************************************'

--Debug (if needed)

--SELECT * FROM @JobsToRunTable

WHILE 1=1 AND @AreJobsRunning = 1

BEGIN

--This has to be first with the delay to make sure the jobs

--have time to actually start up and are recognized as 'running'

WAITFOR DELAY @WaitDelay

--Reset for each loop iteration

SET @AreJobsRunning = 0

--Get the currently executing jobs by our user name

INSERT INTO @ExecutionStatusTable

EXECUTE [master].[dbo].xp_sqlagent_enum_jobs 1, @job_owner

--Debug (if needed)

--SELECT 'ExecutionStatusTable', * FROM @ExecutionStatusTable

--select every job to see if it's running

DECLARE dbCursor CURSOR FOR

SELECT x.[Running], x.[JobID], sj.name

FROM @ExecutionStatusTable x

INNER JOIN [msdb].[dbo].sysjobs sj ON sj.job_id = x.JobID

INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName

OPEN dbCursor FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun

--Debug (if needed)

--SELECT x.[Running], x.[JobID], sj.name

-- FROM @ExecutionStatusTable x

-- INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.JobID

-- INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName

WHILE @@FETCH_STATUS = 0

BEGIN

--bitwise operation to see if the loop should continue

SET @AreJobsRunning = @AreJobsRunning | @IsJobRunning

UPDATE @JobsToRunTable

SET Running = @IsJobRunning, JobID = @JobID

WHERE JobName = @JobNameToRun

--Debug (if needed)

--SELECT 'JobsToRun', * FROM @JobsToRunTable

SET @CurrentDateTime=GETDATE()

IF @IsJobRunning = 1

BEGIN -- Job is running or finishing (not idle)

IF DATEDIFF(mi, @StartDateTime, @CurrentDateTime) > @TimeoutMinutes

BEGIN

print '*****************************************************************'

print @JobNameToRun + ' exceeded timeout limit of ' + @TimeoutMinutes + ' minutes. Stopping.'

--Stop the job

EXEC [msdb].[dbo].sp_stop_job @job_name = @JobNameToRun

END

ELSE

BEGIN

print @JobNameToRun + ' running for ' + CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + ' minute(s).'

END

IF @IsJobRunning = 0

BEGIN

--Job isn't running

print '*****************************************************************'

print @JobNameToRun + ' completed or did not run. ' + CAST(@CurrentDateTime as VARCHAR)

END

FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun

END -- WHILE @@FETCH_STATUS = 0

CLOSE dbCursor

DEALLOCATE dbCursor

--Clear out the table for the next loop iteration

DELETE FROM @ExecutionStatusTable

print '*****************************************************************'

END -- WHILE 1=1 AND @AreJobsRunning = 1

SET @CurrentDateTime = GETDATE()

print 'Finished at ' + CAST(@CurrentDateTime as varchar)

print CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + ' minutes total run time.'

(Visited 3,613 times, 5 visits today)

Spread the love

SQL: “Watchdog” loop to start and monitor SQL Agent Jobs

ByDavid Lozinski

SQL “Watchdog” loop to start and monitor SQL Agent Jobs

Code:

Related Post

Massive SQL Date Time Cheat Sheet

SQL: get max length of values in every table column

C#: Read lines from a text file in batches and process in parallel

You missed

Comparing Microsoft Copilot, Copilot M365, and ChatGPT

Dating Multiple AI’s

Choosing CloudFlare over Microsoft Azure

Convert a Text field to Proper Case using Power Automate