How To Manage SQL Server Job Failure Emails with MS Outlook Conditional Formatting

Some scheduled jobs send failure emails upon an error and DBAs need to remember to fix the job and re-execute it. I use filter rules in MS Outlook to organize my inbox by redirecting all the scheduled job emails to a separate folder. In addition to that, I use conditional formatting rules to keep track of action items as pending or done. The conditional formatting rules highlight the job failure emails with the red color and turn them green when marked complete as shown in the following image.

JobEmailsAsTasksExpected

Create a Conditional Formatting Rule to Highlight a Failed Job Email in Red:

Navigate to the folder where you would like to modify the view. You can even customize your main Inbox folder view, if that is where your emails are. Right-click on the header row and go to View Settings.

JobEmailsAsTasks1

Click on Conditional Formatting.

JobEmailsAsTasks2

In the Conditional Formatting window, click on Add and rename the new untitled rule. Then change the font color to red and click on Condition.

JobEmailsAsTasks3

In the Filter window, put a sufficiently unique text phrase from the error email that can distinguish it from good emails. Also specify in the drop-down where exactly the filter should look for the text phrase i.e. just in the email subject or, email body or both.

My emails have the following text in the email body that I can use for identification –

STATUS:  Failed

Now this a simple example with a text phrase for filtering but you can use other criteria in More Choices and Advanced tabs too for more complex scenarios as necessary. If you get emails with different text patterns then you can always create a new rule for each phrase.

JobEmailsAsTasks4

That is it. Click on OK to come back to your inbox and verify that the error emails have a red font.

Create a Conditional Formatting Rule to Highlight a Failed Job Email in Green After Resolution:

Now we want another rule that will change the red email to a green one when it is marked as complete. Add another Conditional Formatting rule, rename it and change the font color to green and click on Condition.

JobEmailsAsTasks5

In the filter window, follow the same steps as in the previous red rule. Then go the More Choices tab.

JobEmailsAsTasks6

In the More Choices tab, check the Only Items which: and select the drop-down value of are marked complete.

JobEmailsAsTasks7

Back in the Conditional Formatting window, move the green job up. This is important otherwise the red rule will override the effects of the green rule.

JobEmailsAsTasks8

The final window will look like this.

JobEmailsAsTasks9Final

Now when ever the issue from the failure email is resolved, just right-click on the email, go to Follow Up, select Mark Complete. The email will turn green.

JobEmailsAsTasks10

Use a One-Time Schedule for Adhoc Job Executions

If there is ever a need to run a SQL Server job on an adhoc basis outside of its normal schedule, don’t change its original schedule. Rather, create a new one-time only schedule for that job and leave it like that even after the the execution.

If you modify the original schedule then you’d have to remember to revert it back. This can be easily missed out and then the job would be running at the modified schedule after that! Oh, so you say you have a water-tight reminder mechanism and you’ll never forget to revert it?! Please read the next line.

If there was a requirement to run the job out-of-schedule once, then it could happen again too. The one-time schedule would be handy at that time and can be reused.