Archive for the ‘Operator’ Category

How to create an operator and configure SQL Server Agent to notify the operator in case of job failures?

Wouldn’t it be nice if you can get SQL Server to notify you of an event that has occurred or only if there are any job failures so that you can enjoy your Monday Night Football game without constantly checking your laptop to see the latest job executions? Yes. It would be nice and SQL Server does have that feature – operators. Operators are accounts that can receive notifications when an event occurs or a job fails via email, pager, or net send. There’s a caveat that you will need to be aware of when enabling job failure alerts, you must enable the “mail profile” or else no email notification can be sent. The following documents the steps on how to:

  1. Create an operator to receive event or job failure notifications.
  2. Configure the SQL Server Agent to enable the mail profile and define the fail-safe operator as a catch-all operator so that you won’t miss out a thing. SQL Server Agent service restarted required.
  3. Update a SQL Server Agent job to enable job failure notification.

Here are the scripts for each step:

  1. Create an operator.
  2. -- create an operator
    USE [msdb] 
    GO 
    EXEC msdb.dbo.sp_add_operator 
    		@name=N'007' -- name the operator
    		,@enabled=1
    		,@weekday_pager_start_time=0
    		,@weekday_pager_end_time=235959
    		,@saturday_pager_start_time=0
    		,@saturday_pager_end_time=235959 
    		,@sunday_pager_start_time=0
    		,@sunday_pager_end_time=235959
    		,@pager_days=127
            ,@email_address=N'emailaddress@company.com' -- modify the email address here
    GO
    
  3. Configure the SQL Server Agent to enable the mail profile and define the fail-safe operator as a catch-all operator so that you won’t miss out a thing. SQL Server Agent service restarted required.
  4. -- find and write down your database mail profile
    SELECT name as DatabaseMailProfile
    FROM [msdb].[dbo].[sysmail_profile]
    
    -- Enable the database mail profile in the SQL Server Agent Alert System
    -- Restart SQL Server Agent service for this to take effect
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_set_sqlagent_properties 
    @email_save_in_sent_folder=0 -- option to save copies of the sent messages in the Sent Items folder
    GO
    EXEC master.dbo.xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
    N'UseDatabaseMail', 
    N'REG_DWORD', 
    1
    GO
    EXEC master.dbo.xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
    N'DatabaseMailProfile', 
    N'REG_SZ', 
    N'YourDatabaseMailProfile' -- database mail profile
    GO
    
    -- Define a fail-safe operator
    EXEC master.dbo.sp_MSsetalertinfo 
    @failsafeoperator=N'007', -- modify to your operator here
    @notificationmethod = 1; -- notify using email
    
  5. Update a SQL Server Agent job to enable job failure notification.
  6. -- Update a job to notify on failure
    USE [msdb] 
    GO 
    EXEC msdb.dbo.sp_update_job 
    	@job_name=N'syspolicy_purge_history' -- change the job name here
    	,@notify_level_email=2 -- notify on failed job
        ,@notify_email_operator_name=N'007' -- change to your operator here
    GO
    

REFERENCES:

sp_add_operator
http://msdn.microsoft.com/en-us/library/ms186747(v=sql.105).aspx

sp_update_job
http://msdn.microsoft.com/en-us/library/ms188745(v=sql.105).aspx