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.
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.
As you can see I have a new window connected to 34 out of 35 servers that I have registered in this test environment.
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 dbagroup@YOURDOMAIN.com and they had now made it sqldbagroup@YOURDOMAIN.com 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 = ‘sqldbagroup@YOURDOMAIN.com’)
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
- Impress your boss and coworkers by cleaning up your environment so you don’t miss failed job notifications
- Manage Multiple Servers Using Central Management Server
- SQL Server Agent
