How to Identify and Fix Incorrect SQL Server Operator Settings


By:   |   Updated: 2013-03-26   |   Comments (2)   |   Related: More > SQL Server Agent


Problem

As DBA's we rely heavily on email notifications when things go wrong in our environment such as failed jobs or alerts. In practice this is usually done through Operators in the SQL Server Agent. Recently I was working with a client and noticed a job that had been failing for as far back in time as there was history for that job. They were using the best practice of creating Operators and setting jobs to email that operator on failure. Why was I not receiving any emails, this could be business critical and job impacting. Database mail was tested and worked fine, the agent alert system was set up correctly to use the Database Mail profile.

Solution

This environment had over 50 servers going as far back as SQL 2000. I figured the best way to simplify this task was the use of the Central Management Server feature to formulate a query to retrieve information about their standard DBAGroup operator. That was the name they chose for their DBA group and yours may be different in your environment.

There are previous tips on Central Management Server as setting that up is beyond the scope of this tip.

First thing was to open the Central Management Server in SSMS.

Central Management Server

Next, right click at what level you would like to verify, in my case I wanted to verify everything so I clicked on the CMS server and selected New Query.

CMS New Query Selection

As you can see I have a new window connected to 34 out of 35 servers that I have registered in this test environment.

CMS New Query

Missing Operator

The first phase of my cleanup was to identify any servers that did not have their standard DBAGroup operator set up. Below is the query I used for this.

--Find Servers Missing DBAGroup Operator
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBAGroup')
select @@SERVERNAME

After running this I had a list of servers that did not have the DBAGroup operator and it can easily be added by connecting and running sp_add_operator. That was easy enough and saved so much time, rather than having to connect to each server and browse through the SSMS GUI looking at Operators under the SQL Server Agent.

Incorrect Operator Email Address

Another thing I noticed was the email address had changed when they decided to split out SQL and Oracle DBA teams. It used to be [email protected] and they had now made it [email protected] and some of the older servers had not been changed. With that I now wanted to find servers that had the wrong email address. To do that I ran the following in my Central Management Server query window.

--Find Servers with incorrect email address
IF NOT EXISTS(select '1' from msdb..sysoperators where name = 'DBAGroup' 
          and email_address = '[email protected]')
select @@SERVERNAME

After running this I had a nice, quick list of servers that I needed to change the email address for the Operator. This can easily be done using sp_update_operator.

Old Operators Not Being Used

As a final part of my cleanup, I saw old DBA's names as operators that have been gone for 5+ years. Some of the older servers had many operators that I noticed were not being used by jobs or alerts. With this, I wanted to identify all operators that were not set up to be notified in any job or alert, and look at deleting them as part of my cleanup process. After all, if I am already digging up and making sure everything conforms to the company standards, I may as well perform a full cleanup so everything is nice and clean. Below is the SQL used to identify operators that are not set up to be notified by any jobs or alerts. The results can easily be dropped using sp_delete_operator.

--List Operators with no alerts or jobs
select name, email_address from msdb.dbo.sysoperators
where ID not in 
(
     SELECT DISTINCT notify_email_operator_id from msdb..sysjobs
     UNION
     SELECT DISTINCT notify_netsend_operator_id from msdb..sysjobs
     UNION
     SELECT DISTINCT notify_page_operator_id from msdb..sysjobs
     UNION 
     SELECT DISTINCT operator_id from msdb..sysnotifications
)
order by name

As you can see using these simple queries through my Central Management Server I was able to get everything up to current standards by fixing incorrect email addresses, identifying servers that are missing the operator, as well as obsolete operators that are not being used. Once I was finished, my entire enterprise was clean and I started receiving the alerts I was expecting when jobs failed.

 
Next Steps


Last Updated: 2013-03-26


get scripts

next tip button



About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips
Related Resources





Comments For This Article




Friday, September 27, 2013 - 11:15:32 AM - Adam S Back To Top (26975)

This is a great starting guide on how to find out the configurations of the servers and how to verify them going forward.

I also include version checks and server configuration checks. If they are wrong I also correct them in the this script to ensure uniformity in my servers.

 

 


Friday, March 29, 2013 - 1:28:38 AM - Gopalakrishnan Back To Top (23066)

Excellent and with Good Example.



download





Recommended Reading

Different ways to execute a SQL Agent job

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables














get free sql tips
agree to terms