Alert when a SQL Server Agent job fails

SQL Server Agent notifies operators by email, and that needs Database Mail. With BugsRadar a failed job arrives in Telegram, Discord or Pushover instead: which job, which step and the error it reported.

How it works

You add one last step to the job that sends the alert, and every other step jumps to it when it fails. When all steps succeed, the job ends before it reaches the alert step.

You need a BugsRadar project with a channel and the project's API key: see the quick start. The steps below work in SQL Server Management Studio on SQL Server for Windows.

Add the alert step

1. On the Steps page of the job, add a last step named Alert BugsRadar of type PowerShell with the script below, and put your project's API key in it.
2. On the Advanced page of every other step, set On failure action to Go to step: Alert BugsRadar. For the step right before the alert, also set On success action to Quit the job reporting success.
3. For the alert step itself, set On success action to Quit the job reporting failure: the job history keeps showing the failure.

# Windows PowerShell 5.1 doesn't always use TLS 1.2 by itself
[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12

$server = '$(ESCAPE_SQUOTE(SRVR))'  # SQL Server Agent puts the server name here before the step runs
$uri = 'https://api.bugsradar.com/api/v3/notify?category=SQL+Server+Agent&host=' + [Uri]::EscapeDataString($server)

Invoke-RestMethod -Method Post -Uri $uri `
  -Headers @{ 'X-Api-Key' = '<your project API key>' } `
  -ContentType 'text/plain; charset=utf-8' -Body "Job Nightly backup failed on $server"

SQL Server Agent has no token for the job name, so this version has it written in the text. The next one finds the job and the failed step by itself.

Report the failed step and its error

The job history already holds the step that failed and the message it wrote. This version of the alert step reads them from msdb, so one script fits every job:

[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12

$server = '$(ESCAPE_SQUOTE(SRVR))'

# The failed step of the current run: history rows after the previous run's outcome
$failed = Invoke-Sqlcmd -ServerInstance $server -Database msdb -Query @"
SELECT TOP (1) j.name AS job_name, h.step_name, h.message
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.job_id = $(ESCAPE_NONE(JOBID)) AND h.step_id > 0 AND h.run_status = 0
  AND h.instance_id > ISNULL((SELECT MAX(instance_id) FROM dbo.sysjobhistory
                             WHERE job_id = h.job_id AND step_id = 0), 0)
ORDER BY h.instance_id DESC;
"@

if ($failed) {
    $text = 'Job {0} failed on {1} at step {2}: {3}' -f $failed.job_name, $server, $failed.step_name, $failed.message
} else {
    $text = 'Test alert from SQL Server Agent on {0}' -f $server
}

$uri = 'https://api.bugsradar.com/api/v3/notify?category=SQL+Server+Agent&host=' + [Uri]::EscapeDataString($server)
Invoke-RestMethod -Method Post -Uri $uri `
  -Headers @{ 'X-Api-Key' = '<your project API key>' } `
  -ContentType 'text/plain; charset=utf-8' -Body $text

SQL Server Agent treats every $(…) in a step as a token, which is why the script builds the text with -f rather than with PowerShell's $(…) inside strings. The step runs as the SQL Server Agent service account, which can read msdb by default.

CmdExec with curl

On Windows Server 2019 and later the alert step can also be of type Operating system (CmdExec) with the curl.exe that comes with Windows:

curl.exe -fsS --max-time 15 -H "X-Api-Key: <your project API key>" --data-binary "Job Nightly backup failed on $(ESCAPE_DQUOTE(SRVR))" "https://api.bugsradar.com/api/v3/notify?category=SQL+Server+Agent"

Test the alert

In SQL Server Management Studio, right-click the job, choose Start Job at Step… and pick Alert BugsRadar. The message arrives within seconds; the history version sends a test alert when the run has no failed step.

What you receive

A sample from Telegram:

A job that fails every night brings a full message every night. If it fails again within minutes, the repeats arrive as a summary: see repeats and grouping.

The key is written in the job step, so everyone who can open the job sees it. If it leaks, press Regenerate key on the project's page in the web app.