Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails?

I am wondering because I would like to set up an e-mail anytime an SSRS subscription fails - and all of these subscriptions are jobs in the category Report Server.

EDIT - it turns out that when an SSRS subscription fails, the job itself does not fail, so my question will not apply to the SSRS subscription monitoring use. However I would still like to know for other jobs that we run in our environment

share|improve this question
    
Does a step fail at least? My answer below looks at jobs in the category 'Report Server' but if you just want all jobs you can remove that whole AND EXISTS portion of the INSERT/SELECT. And probably change the name ReportServerJob_FailQueue to something more generic. :-) –  Aaron Bertrand May 18 '12 at 15:25
    
Unfortunately, no step fails - but I'm confident I can come up with some other monitoring mechanism! –  JHFB May 18 '12 at 16:51

1 Answer 1

up vote 8 down vote accepted

You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once.

USE msdb;
GO

CREATE TABLE dbo.ReportServerJob_FailQueue
(
  job_id UNIQUEIDENTIFIER,
  run_date INT,
  run_time INT, -- horrible schema, just matching sysjobhistory
  sql_message_id INT,
  sent BIT NOT NULL DEFAULT 0,
  PRIMARY KEY (job_id, run_date, run_time)
);

So then your code, that you can schedule in a job, becomes:

INSERT dbo.ReportServerJob_FailQueue
  (job_id, run_date, run_time, sql_message_id)
SELECT job_id, run_date, run_time, sql_message_id
FROM msdb.dbo.sysjobhistory AS h
WHERE step_id = 0 
AND run_status = 0
AND EXISTS 
(
  SELECT 1 FROM msdb.dbo.sysjobs AS j
    INNER JOIN msdb.dbo.syscategories AS c
    ON j.category_id = c.category_id
    WHERE j.job_id = h.job_id
   AND c.name = 'Report Server'
)
AND NOT EXISTS 
(
  SELECT 1 FROM dbo.ReportServerJob_FailQueue
    WHERE job_id = h.job_id
    AND run_date = h.run_date
    AND run_time = h.run_time
);

Now I assume you want to send an individual e-mail for each failure, so this could be part of the job as well (or part of a different job, though that isn't necessarily wise):

DECLARE 
  @subject NVARCHAR(4000),
  @body NVARCHAR(4000),
  @name SYSNAME,
  @id UNIQUEIDENTIFIER,
  @date INT,
  @time INT,
  @msg INT;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT q.job_id, q.run_date, q.run_time, q.sql_message_id, j.name
  FROM dbo.ReportServerJob_FailQueue AS q
  INNER JOIN msdb.dbo.sysjobs AS j
  ON q.job_id = j.job_id
  WHERE q.sent = 0;

OPEN c;

FETCH NEXT FROM c INTO @id, @date, @time, @msg, @name;

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @subject = 'Report Server job ' + @name + ' failed.';
  SET @body = 'Error number: ' + RTRIM(@msg);

  BEGIN TRY
    EXEC msdb.dbo.sp_send_dbmail 
      @profile_name = 'default',     -- you may need to change this
      @recipients   = 'foo@bar.com', -- you will need to change this
      @subject      = @subject,
      @body         = @body;

    UPDATE dbo.ReportServerJob_FailQueue
      SET sent = 1 
      WHERE job_id = @id
      AND run_date = @date
      AND run_time = @time;
  END TRY
  BEGIN CATCH
    PRINT 'Will have to try that one again later.';
  END

  FETCH NEXT FROM c INTO @id, @date, @time, @msg, @name;
END

CLOSE c; DEALLOCATE c;

There are some other options too:

  • pull in sysjobhistory.message
  • look at individual steps that failed
  • only send a message for any job once in n minutes/hours even if there are multiple failures
  • send a single e-mail with a list of all the jobs that have failed, instead of an e-mail for each failure
  • you might want to include the run_date and run_time in the message, since the e-mail may not be sent or received quickly enough to be an accurate measure of when the job actually failed (I did not include it here because their horrible data type choices make formatting that stuff a royal PITA)
  • you'll probably want to clean up old rows after some time, so a purge command may be desired as well

If Database Mail isn't already set up, please see this tutorial.

You could also use 3rd party tools (e.g. SQL Sentry Event Manager) that will make a lot of this simpler. Full disclosure: I work for SQL Sentry.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.