2014-10-08

SQL Agent check error count step

I had to create a workaround on a database backup job for a server withe 50+ important databases.
To be sure that an attempt is made to take a backup on each database, have some history easy to access and also a option to reconfigure the job for en extra backup on one or more databases I set up one job step for a backup of each database.
This job structure could be illustrated like this:

  1. job step 1: Backup database alpha.
  2. job step 2: Backup database beta.
  3. job step 3: Backup database gamma.
  4. etcetera...
Each job step continues to the next no matter if the job step fails or not. This is to ensure that an attempt to backup each database is made. This structure will give a false status if the last jobs step does not fail.

To ensure a true job status I have added a final control job step, that check the status of the previous job steps.
The status of the previous job steps I get from the table msdb.dbo.sysjobhistory where I filter on the job start time by inserting SQL Agent Tokens in the T-SQL statement:
DECLARE @error_count INT = (
SELECT COUNT(*)
FROM [msdb].[dbo].[sysjobhistory]
WHERE job_id = $(ESCAPE_NONE(JOBID))
  AND [run_date] = $(ESCAPE_NONE(STRTDT))
  AND [run_time] >= $(ESCAPE_NONE(STRTTM))
  AND [run_status] <> 1);

IF (@error_count > 0)
BEGIN
  DECLARE @error_msg NVARCHAR(2047) = N''Error in Database Full Backup job. Check log file for details. (Error Count = '' + CAST(@error_count AS NVARCHAR(3)) + N'' job steps).'';
  RAISERROR(@error_msg, 19, 0) WITH LOG;
END
ELSE
BEGIN
  RAISERROR(''Database Full Backup job conpleted with no errors.'', 0, 0) WITH NOWAIT;
END

This control jobs step generates a error if a previous job step in the same job has failed. The plan (hope) is that the general monitoring platform like Microsoft System Center will catch the error and raise an incident to the Service Desk.

No comments: