3

I have a maintenance plan on my SQL 2008 box - I would like it to send an email address when (or if) it fails. I've set up database mail, and can send a test email..

How can I do this?

alex
  • 1,720
  • 15
  • 43
  • 63

3 Answers3

6
  1. Configure SQL Server Agent to use database mail
  2. Create operator(s) mail, pager, hours, etc.
  3. Configure maintenance plan to email appropriate operator(s) based on exit condition.

http://msdn.microsoft.com/en-us/library/ms186358.aspx

http://technet.microsoft.com/en-us/library/ms175962.aspx

jscott
  • 24,484
  • 8
  • 79
  • 100
  • I've done this... but i can't seem to do number 3...? Where do i find the exit conditions on my tasks? i only have a green arrow coming out of the event box on the designer... – alex Jan 16 '10 at 02:44
  • 3
    You don't. Edit the SQL Agent job and configure the job to send you an email on job failure. – mrdenny Jan 16 '10 at 11:08
  • 1
    If you want to be notified per task (when a task fails, rather than when a plan fails), double click on a green arrow to open its properties and change the Value to Failure. You can create multiple arrows ("precedence constraints"?) from a single task, so you can have one which performs the next task upon Completion, and another which sends an email on Failure. If the task fails, the condition for both arrows will be met and both the next task and the email task will be run. – Dave May 03 '13 at 20:58
1

Add an Execute SQL task to your maintenance plan that makes the call to sp_send_dbmail and connect the failure routes of any of your other tasks to this task.

squillman
  • 37,883
  • 12
  • 92
  • 146
0

We added am "Execute T-SQL Statement Task", which calls a stored procedure, with the name of the Maintenance Plan as a paramater:

EXEC server.schema.SP_SENDALERT 'plan.subplan';

In that stored procedure, we query back to the SYSJOBS and SYSJOBHISTORY for the plan to obtain the message for the last 'failure', which we then send to 'IT Support' as part of a send_dbmail (which is driven by a procedure that loop through unsent messages in the MESSAGING table), something along the lines of this:

CREATE PROCEDURE SP_SENDALERT (@plan VARCHAR(MAX))
AS
BEGIN

    DECLARE @error VARCHAR(MAX)

    SELECT TOP 1 @error = SJH.[MESSAGE]FROM MSDB.DBO.SYSJOBS SJ
    LEFT JOIN MSDB.DBO.SYSJOBHISTORY SJH ON SJ.JOB_ID = SJH.JOB_ID
    WHERE SJ.ENABLED = 1
    AND SJH.RUN_STATUS = 0
    AND SJ.[NAME] = @plan
    ORDER BY SJH.RUN_DATE, RUN_TIME DESC

    INSERT INTO database.schema.MESSAGING
    (MESSAGE_TYPE, MESSAGE_SUBJECT, MESSAGE_BODY, RECIPIENTS, RECORD_ADDED)
    VALUES  (1, 'ERROR: ' + @plan, @error, 'itsupport@x.com', GETDATE())

END
Sean
  • 101
  • 3