By: Arshad Ali | Comments (13) | Related: > Service Broker
Problem
As a DBA, we often need a way to track what events are occurring (changes that are being made) on SQL Server or at the database level. So what is a way to track these changes without much impact on SQL Server performance?
Solution
SQL Server 2005 and onwards provides event notifications mechanism for tracking all events or changes happening at the database or instance level. This can be done with DDL triggers or SQL traces, but event notifications have the advantage of being asynchronous in nature and running outside of the scope of the transaction and hence it can run inside (or be a part of) a database application to capture defined events without using the resources allocated to the transaction.
Event notification is a programmatic alternative to using DDL triggers or SQL traces and executes in the response to different DDL statements, SQL traces, Service Broker events (like QUEUE_ACTIVATION or BROKER_QUEUE_DISABLED) and then sends this information, in XML format, to the SQL Server Service Broker (SSB) service. In other words, when you create a notification, SQL Server tracks the defined events and the occurrence of that event is written to the SSB service and then you can retrieve this information asynchronously from the SSB queue when needed. Discussion of Service Broker is out of the scope of this tip, but you can learn more about SQL Server Service Broker here.
Let's get started...
In Script #1, I am first checking if Service Broker is enabled at the database level and if not then I am enabling it. Next I am creating a SSB queue and the SSB service will use this queue to store messages coming to this service. This service uses the built-in contract (a contract defines a type of message that can be sent to the SSB service), http://schemas.microsoft.com/SQL/Notifications/PostEventNotification, which is specifically for event notification. Note: you will need exclusive access to the database to run the alter database command.
| Script #1 - Setting up environment for demonstration |
|---|
|
--Check if the database is enabled for Service Broker --If not then enable it IF EXISTS (SELECT * FROM sys.databases WHERE name = 'AdventureWorks' AND is_broker_enabled = 0) ALTER DATABASE AdventureWorks SET ENABLE_BROKER; GO USE AdventureWorks GO --Create a queue which will hold the tracked information CREATE QUEUE dbo.EventNotificationQueue GO --Check if the queue is created or not SELECT * FROM sys.service_queues WHERE name = 'EventNotificationQueue' GO --Create a service on which tracked information will be sent CREATE SERVICE [//AdventureWorks/EventNotificationService] ON QUEUE dbo.EventNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO --Check if the service is created or not SELECT * FROM sys.services WHERE name = '//AdventureWorks/EventNotificationService' GO |
In Script #2, I am creating two database level notifications. The first will notify when there is a create table command execution and the second will notify when there is an alter table command execution. You can also create a notification event group; for example you can create a single notification with DDL_TABLE_EVENTS to track all events of a table like creation, alteration and drop.
| Script #2 - Creating database level notification |
|---|
|
--Create a notification to track create table command CREATE EVENT NOTIFICATION NotifyCREATETABLEEvents ON DATABASE FOR CREATE_TABLE TO SERVICE '//AdventureWorks/EventNotificationService' , 'current database' GO --Create a notification to track alter table command CREATE EVENT NOTIFICATION NotifyALTERTABLEEvents ON DATABASE FOR ALTER_TABLE TO SERVICE '//AdventureWorks/EventNotificationService' , 'current database' GO --Check if both the above notifications created or not SELECT * FROM sys.event_notifications WHERE name IN ('NotifyCREATETABLEEvents','NotifyALTERTABLEEvents') GO |
In Script #3, I am creating a server level notification that will be raised whenever an error is raised at the SQL Server instance level. You can query the sys.server_event_notifications catalog view to check if a notification exists on the server or not.
| Script #3 - Creating server level notification |
|---|
|
--Create a notification to error occuring at server level CREATE EVENT NOTIFICATION NotifyERROREvents ON SERVER WITH FAN_IN FOR ERRORLOG TO SERVICE '//AdventureWorks/EventNotificationService', 'current database' GO --Check if the above notification was created or not SELECT * FROM sys.server_event_notifications WHERE name IN ('NotifyERROREvents') GO |
In Script #4, let's verify if these event notifications are actually working. In this script, I am creating a table, then altering it, which will be tracked by both the database level event notifications we created above and then I am using RAISERROR (WITH LOG clause is required to be tracked by server level event notification) to generate an error in SQL Server which will be tracked by our last server level event notification.
| Script #4 - Generating database and server level events |
|---|
|
--Generate a create table event CREATE TABLE ABC ( COL1 INT, COL2 INT ) GO --Generate an alter table event ALTER TABLE ABC ADD COL3 INT GO --Generate a server level event RAISERROR (N'Generating error for Event Notification testing...', 16, 1) WITH LOG GO --Review if the events were tracked in queue SELECT CAST(message_body AS XML) AS message_in_xml FROM dbo.EventNotificationQueue GO |
In Script #5, event notifications send tracked information to the SSB service in XML format; you can query the queue to see the tracked information, but you need to use the RECEIVE command to retrieve messages from the queue, process them and remove them from the queue as shown below. With the RECEIVE command you can specify the number of records you want to retrieve at one time. In this script, I am using a TOP(1) command to retrieve the top 1 record form the queue and displaying the information that it contains. Event notification sends information in XML format (remember we used the built-in contract with the service we created that defines that only XML data can be written to the service) and that's the reason I am casting the message body to an XML data type. Since I am using a TOP(1) clause with the RECEIVE command, I ran the below command 3 times, since there were 3 records in the queue. You can see the result in the image below the query. You can also use a looping construct to read all the records from the queue instead of running the same script multiple times.
| Script #5 - Retrieving records from the queue |
|---|
|
DECLARE @TargetDialogHandle UNIQUEIDENTIFIER; DECLARE @EventMessage XML; DECLARE @EventMessageTypeName sysname; WAITFOR ( RECEIVE TOP(1) @TargetDialogHandle = conversation_handle, @EventMessage = CONVERT(XML, message_body), @EventMessageTypeName = message_type_name FROM dbo.EventNotificationQueue ), TIMEOUT 1000; SELECT @TargetDialogHandle AS DialogHandle, @EventMessageTypeName AS MessageTypeName, @EventMessage.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ) as EventType, @EventMessage.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)' ) as ServerName, @EventMessage.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)' ) as DatabaseName, @EventMessage.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' ) as LoginName, @EventMessage.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS TSQLCommand, @EventMessage.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(128)' ) AS TextData, @EventMessage.value('(/EVENT_INSTANCE/Severity)[1]', 'varchar(128)' ) AS Severity, @EventMessage.value('(/EVENT_INSTANCE/Error)[1]', 'varchar(128)' ) AS ErrorNumber |
In Script #6, you can use this code to cleanup and drop all the objects, in the reverse order of their creation, which we created for this demonstration.
| Script #6 - Cleaning up the objects created |
|---|
|
DROP EVENT NOTIFICATION NotifyCREATETABLEEvents ON DATABASE GO DROP EVENT NOTIFICATION NotifyALTERTABLEEvents ON DATABASE GO DROP EVENT NOTIFICATION NotifyERROREvents ON SERVER GO DROP TABLE ABC GO DROP SERVICE [//AdventureWorks/EventNotificationService] GO DROP QUEUE dbo.EventNotificationQueue GO |
Permission Requirements
- In order to create a database level event notification, you need to have CREATE DATABASE DDL EVENT NOTIFICATION permissions in that database. To drop it, you must be the owner of the event notification or have ALTER ANY DATABASE EVENT NOTIFICATION permission in that database.
- In order to create a server level notification, you need to have CREATE DDL EVENT NOTIFICATION permissions on the server. To drop it, you must be the owner of the event notification or have ALTER ANY EVENT NOTIFICATION permission on the server.
- In order to create an event notification to track SQL traces, you need to have CREATE TRACE EVENT NOTIFICATION permission on the server. To drop it, you must be the owner of the event notification or have ALTER ANY EVENT NOTIFICATION permission on the server.
- In order to create a queue scoped event notification you need ALTER permission on the queue. To drop it, you must be the owner of the event notification or have ALTER permission on the queue.
Notes
- You can query sys.event_notification_event_types to get the list of all the events on which you can create event notifications or you can view DDL events, DDL event groups, SQL trace events, SQL trace event groups.
- To see the differences between event notifications and triggers click here and differences between event notifications and SQL traces click here.
- You cannot directly alter a notification, you need to drop and re-create it.
Next Steps
- Review SQL Server 2005 Service Broker Sample tip.
- Review SQL Server Service Broker example on how to configure, send and receive messages tip.
- Review Understanding Event Notifications on technet.
- Review CREATE EVENT NOTIFICATION T-SQL command on technet.
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
