Alert on Scheduled SQL Jobs – Missed to enable

During the deployments, windows patching or any other activity, DBAs often disable the jobs to avoid data corruption and jobs failure. Once the activity is completed, DBAs need to re-enable them.

Since it is a manual intervention, it is possible that a job might be left behind from re-enabling. It happened in my case long time ago and since then I have been extra cautious not to repeat the same mistake again.

I thought to create an alert mechanism that shall help me in identifying if anything is missed.

Sharing the code below, hoping this will help you as well.

Steps:

1) Fetch all the active jobs information in a table

2) Compare the information with the current status

3) Unmatched information should be emailed.

Code

USE MSDB
GO

CREATE TABLE dbTest.dbo.tbActiveJobs(
JobID NVARCHAR(MAX),
JobName VARCHAR(MAX),
isEnabled BIT,
dtUpdTime DATETIME)

INSERT INTO dbTest.dbo.tbActiveJobs
SELECT job_id, name, enabled, GETDATE() FROM msdb.dbo.sysjobs
WHERE enabled=1;

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [job_id] AS ‘td’,”,
[name] AS ‘td’
FROM MSDB.dbo.sysjobs a
JOIN dbTest.dbo.tbActiveJobs b ON a.job_id = b.JobID
WHERE b.isEnabled <> a.enabled
FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))

SET @Body =
‘<html><head>’
+ ‘<style>’
+ ‘th {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} ‘
+ ‘td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} ‘
+ ‘</style>’
+ ‘</head>’
+ ‘<body>’
+ ‘<h4>Please enable the below mentioned job(s)</h4>’
+ ‘<table border=”1″>’
+ ‘<tr>’
+ ‘<th>job_id</th>’
+ ‘<th>name</th>’
+ ‘</tr>’

SET @Body = @Body + @xml +'</table></body></html>’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLProfile’, — replace with your SQL Database Mail Profile
@body = @body,
@body_format =’HTML’,
@from_address = ‘DisabledJobs <shaikshafi.nabi@gmail.com>’,
@recipients = ‘ shaikshafi.nabi@gmail.com’, — replace with your email address
@subject = ‘Disabled Jobs’;