How to Auto Identify SQL Server Operator and Add It to Job Notification

Monitoring the status of SQL server jobs is very important. If there is corrupted SQL Server agent, then jobs would not run as scheduled. So make sure you repair corrupted SQL Server before proceeding. Auto identifying operator and adding it to job notification on hundreds of servers is a hectic task. However with this script you can easily monitor all your SQL server jobs.

Set operators

SQL Server operators allow people or groups to receive notifications when jobs complete on SQL server or whenever there is an alert. SQL Server allows 3 types of notification to these operators

1. Email
2. Pager
3. Netsend

Because of SQL Server’s Database mail, of these 3 notification options, Email is mostly preferred and used in SQL server. You can manually create an operator by following these steps

1. Connect to the target SQL Server’s database Engine
2. Expand the SQL Server’s agent node
3. Right click on the Operators folder under SQL Server agent and then create an operator
4. On the form, enter a name for the operator. Under notification fields, add an email address. You can specify group email address or an individual email address. You can leave the page and net send address fields as empty.

You can create multiple operators but it has to be unique i.e., names of operators has to be different. Please do note that Pager and net send options will be removed from future versions of SQL Server.

Fail safe

Creating operator is not enough, we have to make an operator as fail safe operator. A fail-safe operator is the single point of contact when SQL Server agent is unable to access system tables in the MSDB database. To enable an operator as fail safe, open properties of SQL Server agent and then select an operator and check the Email option under Fail-Safe operator.

Automate Operators and notifications

Rather than you manually creating an operator, by making it failsafe and assigning it to SQL Job’s notification; you can simply execute the script from a new query window. This script will check if an operator already exists with the same name. If not, operator is created and assigned as fail-safe operator. This operator is then assigned to the failure notifications of each SQL Server agent job on that server.

Multi Server

Though this script automates the operator creator and notification on a single SQL Server on which it is executed, you can easily execute it on all your SQL Servers. The easy method is to create registered servers and then execute this script on all registered servers. Another method is to save this script as .sql file and then execute the .sql script on all servers through SQL CMD.

Script:

DECLARE @find INT
DECLARE @operatorname VARCHAR(200)
DECLARE @operatoremail VARCHAR(500)

SET @operatorname = '<OPERATOR NAME>'
SET @operatoremail = '<OPERATOR EMAIL ADDRESS. SEPARATE MULTIPLE EMAIL ADDRESS BY ;'
SET @find = (
        SELECT count(*)
        FROM msdb.dbo.sysoperators
        WHERE NAME = @operatorname
        )

IF @find = 0
BEGIN
    EXEC msdb.dbo.sp_add_operator @name = @operatorname
        ,@enabled = 1
        ,@email_address = @operatoremail
END

EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator = @operatorname
    ,@notificationmethod = 1

DECLARE @operator_id INT

SELECT @operator_id = [id]
FROM msdb.dbo.sysoperators
WHERE NAME = @operatorname

SELECT @operator_id

UPDATE msdb.dbo.sysjobs
SET notify_email_operator_id = 0

UPDATE msdb.dbo.sysjobs
SET notify_level_email = 0

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst email error and excel recovery software products. For more information visit www.datanumen.com