Enhancing SQL Agent Notifications

SQL Agent Communicates Like a Third Grader

... in that it will tell you whatever you want it to say without much of a filter. 

SQL Agent is limited in how it can communicate its successes and failures to interested individuals or teams. Out-of-the-box options for notifications allows for only a single operator (though that operator can be an individual or defined group) and a single message of either completion, success or failure. If you need to communicate to more than one individual or group or want to provide a user-friendly message then you need to roll your own solution. This article gives you the framework for doing so by using a system stored procedure and some creative flow mechanisms.

Imagine a situation where beyond simply notifying the DBA Team that a backup completed successfully you want to notify the Application Analyst and perhaps a key (aka Super) user that this process completed in a less verbose (and technical) bent than what is output as part of the completion notification process in SQL Agent. I find this fairly useful when setting up scheduled SQL Agent jobs that are involved with backing up the database before a planned upgrade in the application layer, or performing some pre-upgrade tasks such as stopping Change Data Capture collection or running a custom script for which someone other than a DBA has a vested interest in knowing it completed successfully or failed. 

I use SQL Agent as a way to not have to sit in front of a workstation monitoring and executing all my work 24 hours per day and use scheduled jobs in SQL Agent whenever possible for not just that benefit but also to provide structure and documentation for the tasks that I do run.

A Simple Example

I'm going to make the assumption you know how to create a SQL Agent Job. So please do so and create three steps as shown below:

Step 1: Disable CDC and Backup iDBA DB

USE iDBAGOEXEC sys.sp_cdc_disable_dbGOUSE masterBACKUP DATABASE [iDBA] TO  DISK = N'C:BackupiDBA.bak' WITH NOFORMAT, NOINIT,  NAME = N'iDBA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 100;GO

Step 2: NOTIFY SUCCESS

DECLARE @date_start DATETIME, @date_end DATEDECLARE @msg NVARCHAR(MAX)SELECT @msg = CAST('CDC disabled and iDBA DB backed up at ' + CAST(CONVERT(datetime, GETDATE(), 114) AS VARCHAR(100)) AS NVARCHAR(MAX)) + ' on ' + @@SERVERNAMEEXEC msdb.dbo.sp_send_dbmail@profile_name = 'sql_agent_DB_Mail_Profile',@recipients = '[email protected];[email protected];[email protected]',@body = @msg,@subject = 'A Message from your SQL Server';

Step 3: NOTIFY FAILURE

DECLARE @date_start DATETIME, @date_end DATEDECLARE @msg NVARCHAR(MAX)SELECT @msg = CAST('CDC disabled and iDBA DB backup failed at ' + CAST(CONVERT(datetime, GETDATE(), 114) AS VARCHAR(100)) AS NVARCHAR(MAX)) + ' on ' + @@SERVERNAME+ ' DBA is reviewing the issue.'EXEC msdb.dbo.sp_send_dbmail@profile_name = 'sql_agent_DB_Mail_Profile',@recipients = '[email protected];[email protected];[email protected]',@body = @msg,@subject = 'A Message from your SQL Server';

At this point your job steps should flow like this:

What we need to do is go into each Job Step's Advanced tab and change the flow to look like this:

Now, when step 1 completes successfully the users will be notified with a custom message rather than the simple success/completion/failure for the job as a whole which is configured from the Notifications page of the SQL Agent Job. Send that one to the DBAs if required. This flow also allows for notification of successful job completion to the DBA regardless of if Step 2 fails for some reason.  Likewise, if Step 3 fails the DBA can still receive the correct notification of job failure.

Look Mom, No Hands!

This is obviously a simple example of what can be done. In practice, I use this frequently when performing scheduled upgrade tasks or refreshing test/dev/reporting databases and various individuals need to be communicated with as separate steps of the go-live/upgrade/refresh process complete—particularly when I have other things to do! Matter of fact, I used a similar task just this weekend to prepare for a critical upgrade to a new environment for our largest SQL implementation and was on a cross country train with limited Internet access. As I was rolling through various Western states, I was able to get periodic updates without ever having to log into a VPN session.

Of course other options exist. You could never get any sleep and work 24 hours per day. Personally, I like this option better.