Sql-Server-Agent-Jobs

Consolidate Multiple Email Operators

Suppose you’ve been working on cleaning up SQL Agent jobs and their email operators and find that you have more than one operator with the same email address. How would you tidy this up to ease administration?

Let’s do a little setup to demonstrate the scenario.  The T-SQL below will create three operators with different names but the same email address.  Then it updates two of my existing test jobs to use those email operators when the job fails.  I am also logging failures to the Windows Event Log.

Continue reading

Finding Failed Jobs

How would you go about finding jobs that have failed and why would you want to look for these?  You might want to do this to ensure that you are being notified appropriately of job failures. For instance, after reviewing failed jobs, you may realize that there are job failures that no one knew about. Maybe this is occurring because the jobs are not set to send email. If that’s the case see my post here so you can fix that.

Continue reading

Fixing Jobs With No Email Notification

In the last post we found jobs that had no email notification. Once you find them, how do you fix this problem?

I’m going to assume that database mail is already enabled and configured on the SQL Server instance. 1. Create an operator, assuming one does not exist.

This can be done either through T-SQL or through the GUI.  In the GUI, right click the Operator folder under SQL Server Agent and chooser “New Operator.”  Then fill in the details of operator “Name “and “Email name.”  See below for an example.

Continue reading

Finding Jobs With No Email Notifications

When you take over a new SQL Server instance one of the things you might consider doing early on is finding SQL Agent jobs that have no email notification set up.  These will be jobs that could fail and they will never alert anyone. I am of the opinion that if a job is worth creating, then it’s worth telling someone when it doesn’t do what it is supposed to do.

Continue reading