New in SQL Server 2005, Event Notifications provide a flexible and powerful way of capturing and handling events that occur inside of SQL Server. Event Notifications execute for DDL Events and Trace Events inside of the database engine and can be used instead of DDL triggers or SQL Trace to capture information about the event being fired. However, unlike DDL triggers and SQL Trace which process synchronously, Event Notifications utilize Service Broker to asynchronously capture event information. This article will cover the basics behind Event Notifications and how they can be used to create customized responses to events inside of SQL Server 2005 and 2008.

To get started with Event Notifications it is necessary to understand the basic requirements and components of a Service Broker Application. Service Broker is a system for sending and receiving messages that are typed and contain information or data. Service Broker also first debuted in SQL Server 2005. In order to make use of Event Notifications, the first requirement is that Service Broker be enabled on the database that will contain the broker components. By default msdb has Service Broker enabled on it. However, creating new objects in msdb or any of the system databases is not generally considered a best practice. For the purposes of this article and subsequent articles for specific uses of Event Notifications we'll create a new database to hold the objects needed to setup and use Event Notifications.

CREATE DATABASE [EventNotificationsDB]
GO
ALTER DATABASE [EventNotificationsDB]
SET ENABLE_BROKER;
GO
USE [EventNotificationsDB]
GO

Event Notifications utilize a Service Broker Queue for holding messages generated by firing events. The queue is similar to a database table that holds a row for each message that has been added to the queue. To create a queue to hold the messages the CREATE QUEUE DDL command is used:

-- Create a service broker queue to hold the events
CREATE QUEUE EventNotificationQueue

A Service Broker Service manages the task of transmitting messages to a queue based on a defined contract that defines the messages being transmitted. Service Broker has a built-in existing contract for Event Notifications that is used for defining a service to transmit messages generated by Event Notifications.

-- Create a service broker service receive the events
CREATE SERVICE EventNotificationService
 ON QUEUE EventNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

With the Service and Queue created, we can now create an Event Notification on the service that will use the Service to process messages for an Event. In this example we'll use the ERRORLOG Trace Event to create our Event Notification.

-- Create the event notification for ERRORLOG trace events on the service
CREATE EVENT NOTIFICATION CaptureErrorLogEvents
 ON SERVER
 WITH FAN_IN
 FOR ERRORLOG
 TO SERVICE 'EventNotificationService', 'current database';
GO

At this point the Event Notification has been created. To see the objects created, we can query the Service Broker DMV's:

-- Query the catalog to see the queue
SELECT *
FROM sys.service_queues
WHERE name = 'EventNotificationQueue';
GO
-- Query the catalog to see the service
SELECT *
FROM sys.services
WHERE name = 'EventNotificationService';
GO
-- Query the catalog to see the notification
SELECT * 
FROM sys.server_event_notifications 
WHERE name = 'CaptureErrorLogEvents';
GO

To test that the Event Notification is functioning as expected, an error can be generated using RAISERROR and using the WITH LOG option to send the error to the ErrorLog:

-- Test the Event Notification by raising an Error
RAISERROR (N'Test ERRORLOG Event Notifications', 10, 1) WITH LOG;
GO

The EventNotificationQueue will now have a single row containing the EventNotification message for the ErrorLog Event. The queue can be viewed by querying it exactly as if it were a table:

-- Look at data held in the Queue
SELECT *
FROM EventNotificationQueue
GO

The table returned from the queue contains a number of columns including the service_name of the Service that transmitted the message, the message_type_name for the type of message in the row, and the message_body, which is a serialized representation of the message. Multiple services can feed into a single queue. This can be useful as a separation boundary for different types of Event Notifications that should be processed differently. To retrieve the information that was included in the message, CAST the message_body column to XML:

-- Cast message_body to XML
SELECT CAST(message_body AS XML) AS message_body_xml
FROM EventNotificationQueue

The message is a well formed XML document that is not schema bound that has a root node of EVENT_INSTANCE. The information contained in the XML document depends on the event that fired. For the ErrorLog event, the following XML document is returned:

<EVENT_INSTANCE>
 <EventType>ERRORLOG</EventType>
 <PostTime>2009-11-19T23:55:23.950</PostTime>
 <SPID>55</SPID>
 <TextData>2009-11-19 23:55:23.94 spid55 Test ERRORLOG Event Notifications
</TextData>
 <DatabaseID>5</DatabaseID>
 <TransactionID />
 <NTUserName>jkehayias</NTUserName>
 <NTDomainName>KHOME</NTDomainName>
 <HostName>PC-JKEHAYIAS</HostName>
 <ClientProcessID>4776</ClientProcessID>
 <ApplicationName>Microsoft SQL Server Management Studio - Query</ApplicationName>
 <LoginName>KHOME\jkehayias</LoginName>
 <StartTime>2009-11-19T23:55:23.947</StartTime>
 <Severity>10</Severity>
 <ServerName>SQL2K8DEMO</ServerName>
 <Error>50000</Error>
 <DatabaseName>EventNotificationsDB</DatabaseName>
 <LoginSid>AQUAAAAAAAUVAAAAZBFoMuNszXI0IudbumQAAA==</LoginSid>
 <RequestID>0</RequestID>
 <EventSequence>643</EventSequence>
 <IsSystem />
 <SessionLoginName>KHOME\jkehayias</SessionLoginName>
</EVENT_INSTANCE>

While querying the queue allows the messages contained in the queue to be viewed, it doesn't actually remove the message from the queue. A Service Broker queue can be processed manually or automatically by a stored procedure that is bound to the queue and activated through Event Based Activation when messages enter the queue from a service. When processing the messages from the queue, the RECEIVE command is used to remove a message from the queue. Unless you process the messages on the queue by issuing a RECEIVE command, the messages will continue to remain in the queue. When manually processing the queue, the messages can be retrieved one at a time by specifying the TOP (1) operator in the RECEIVE:

DECLARE @message_body xml;

-- Receive the next available message FROM the queue
RECEIVE TOP(1) -- just handle one message at a time
 @message_body=message_body
 FROM EventNotificationQueue;

SELECT @message_body;
GO

Requerying the queue directly after running the above statement, will show that the message was removed from the queue. If you omit the TOP specification the RECEIVE command won't always retrieve every message from the queue. It will retrieve every message for the next conversation_handle in the queue. If there are multiple conversation_handles in the queue, multiple executions of RECEIVE will be needed to completely process the queue. Multiple messages can be processed from the queue using a table variable that has a matching schema to the columns that are being retrieved. The RECEIVE command is then used to process the messages using the INTO operations, specifying the table variable that the messages will be inserted into. Once the messages have been received, the message XML can be parsed using XQuery to retrieve the items of interest from the XML document:

-- Declare the table variable to hold the XML messages
DECLARE @messages TABLE
( message_data xml );

-- Receive all the messages for the next conversation_handle from the queue into the table variable
RECEIVE cast(message_body as xml)
FROM EventNotificationQueue
INTO @messages;

-- Parse the XML from the table variable
SELECT 
 message_data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ) as EventType,
 message_data.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') AS PostTime,
 message_data.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(128)' ) AS TextData,
 message_data.value('(/EVENT_INSTANCE/Severity)[1]', 'varchar(128)' ) AS Severity,
 message_data.value('(/EVENT_INSTANCE/Error)[1]', 'varchar(128)' ) AS Error
FROM @messages;

In contrast to manually processing the queue, it is also possible to automatically process the queue using activation. Activation requires an activation stored procedure that is executed when new messages are added to the queue. The activation procedure is a standard stored procedure that works off the queue instead of tables in the database. SQL Server Management Studio 2008 has a Service Broker Application Template that can be used to expedite the development of the activation stored procedure. To open the template, right click on the Service Broker folder under a database and click on New Service Broker Application.

For the ErrorLog example provided, the activation procedure can be used to send email notifications when an error that has a severity > 10 occurs. To do this requires that the stored procedure be signed with a certificate following the instruction in my last article - Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail.

CREATE PROCEDURE [dbo].[ProcessEventNotifications]
WITH EXECUTE AS OWNER
AS 
SET NOCOUNT ON
DECLARE @message_body xml 
DECLARE @email_message nvarchar(MAX)
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
-- Receive the next available message FROM the queue
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@message_body=message_body
FROM dbo.EventNotificationQueue
), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END 

IF (@message_body.value('(/EVENT_INSTANCE/Severity)[1]', 'int' ) > 10) -- Error is not Informational
BEGIN

-- Generate formatted email message
SELECT @email_message = 'The following event was logged in the SQL Server ErrorLog:' + CHAR(10) +
'PostTime: ' + @message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') + CHAR(10) +
'Error: ' + @message_body.value('(/EVENT_INSTANCE/Error)[1]', 'varchar(20)' )  + CHAR(10) +
'Severity: ' + @message_body.value('(/EVENT_INSTANCE/Severity)[1]', 'varchar(20)' ) + CHAR(10) +
'TextData: ' + @message_body.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(4000)' );
-- Send email using Database Mail
    EXEC msdb.dbo.sp_send_dbmail
             @profile_name = 'SQL Monitor', -- your defined email profile 
             @recipients = 'dbagroup@domain.com', -- your email
             @subject = 'SQL Server Error Log Event',
             @body = @email_message;

END
--  Commit the transaction.  At any point before this, we could roll 
--  back - the received message would be back on the queue AND the response
--  wouldn't be sent.
COMMIT TRANSACTION
END
GO

After creating the activation procedure, the procedure must be signed with a certificate and the Service Broker Queue must be updated so that the stored procedure is activated when a new message is added to the queue. If a certificate login already exists for use with Database Mail, the same certificate can be used to sign the activation stored procedure by following the instructions in the Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail article to backup the certificate, and create it in the database with the activation procedure. The certificate from master that the login was created from can be used for the backup if one doesn't exist in the current database. The ALTER QUEUE DDL statement is used to change the existing queue by specifying the ACTIVATION option. The ACTIVATION option has its own options including:

STATUS - specifies whether or not the queue performs activation or not. Allows for suspension of activation will the queue continues to collect messages
PROCEDURE_NAME - the name of the stored procedure to execute upon queue activation
MAX_QUEUE_READERS - the maximum number of executing instances of the activation procedure that the queue can start concurrently.
EXECUTE AS - the user security account that will be used to execute the activation procedure

-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::[ ProcessEventNotifications]
BY CERTIFICATE [DBMailCertificate] 
WITH PASSWORD = '$tr0ngp@$$w0rd';
GO
--  Alter the Queue to add Activation Procedure
ALTER QUEUE EventNotificationQueue
WITH 
 ACTIVATION -- Setup Activation Procedure
(STATUS=ON,
 PROCEDURE_NAME = [ProcessEventNotifications],  -- Procedure to execute
 MAX_QUEUE_READERS = 1, -- maximum concurrent executions of the procedure
 EXECUTE AS OWNER) -- account to execute procedure under
GO

To test the new activation stored procedure out, execute the RAISERROR that was previously used to test the Queue, and then query the ErrorLogEvents table.

-- Test the Event Notification by raising an Error
RAISERROR (N'Test ERRORLOG Event Notifications', 10, 1)WITH LOG;
-- View Queue Contents
SELECT *
FROM EventNotificationQueue;

No messages should be returned from the EventNotificationQueue, and no email should have been generated by the above error. The severity of the original RAISERROR was only 10 which is not emailed by the stored procedure. However, a severity of 16 should generate an email:

-- Test the Event Notification by raising an Error
RAISERROR (N'Test ERRORLOG Event Notifications', 16, 1) WITH LOG;

Event Notifications in SQL Server 2005 and 2008 can provide near real time notification of changes and events in SQL Server through email. In the my next article I will cover how to use this functionality to get immediate notifications of Deadlocks from SQL Server.