Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Event Notifications in SQL Server for Tracking Changes


By:   |   Last Updated: 2010-09-27   |   Comments (13)   |   Related Tips: More > 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

SQL Server 2005 and onwards provides event notifications mechanism for tracking all events or changes happening at the database or instance level

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


Last Updated: 2010-09-27


get scripts

next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, August 16, 2016 - 8:31:24 AM - Daniel Okeyemi Back To Top

Please can you assist, I have followed this event notification as instructed here and it works well but this only send messages to the queue when their is a schema change. I want to enable change tracking in only 3 tables and only want to receive messages only when the tables has been updated using DML statement. Please can you help.

 


Wednesday, June 10, 2015 - 12:16:37 AM - Vishwanath Back To Top

Hi Arshad,

   Thanks for such a good arcticle. This helped me a lot in understanding Event Notifications using SQL Service Broker. 

 

I have few queries on this to help in solving one my requirement

1. You have used //AdventureWorks/EventNotificationService in creating the service. How do we interpret it? Can I use the same structure to send messages to a remote service? How do i send messages to a Queue that is part of a different database. IF you have already written an article to solve this could please direct me to the link?

Thanks once again

Vishwanath


Monday, June 17, 2013 - 9:42:45 AM - Glen Back To Top

i have a notifcation queue outgrown to 58GB, what can be the reason? how does message queue get purged?

thanks,
Glen


Friday, April 05, 2013 - 2:08:01 AM - Ragini Gupta Back To Top

its really a nice aritcle, i could learn something

Ragini Gupta

sqlserverssis2012.blogspot.com


Friday, January 18, 2013 - 1:01:26 AM - Kumar Back To Top

Hi Arshad,

I am new to Service Broker ,Could you please clarrify me "Event Notifications " are applicable only for external activation.?

I have a task when ever a change is being made to a table  i need to insert records into another 2 tables and I was asked to

implement via event/subscribe process (event notifications) but I believe we can write event notifcations on DDL operations not on DML.

Just confused.Please clarrify me on this

 

Thanks,

Kumar


Monday, August 13, 2012 - 2:09:24 AM - Arshad Back To Top

Well I have not tried this but I suppose it should work. You can find more detail of using service broker across servers here:

http://www.databasejournal.com/features/mssql/article.php/3920581/article.htm

http://msdn.microsoft.com/en-us/library/bb839483(v=sql.105).aspx

 


Friday, August 10, 2012 - 4:42:54 PM - mbourgon Back To Top

Arshad, what's involved in shipping the Events to a secondary server?  It's service broker so definitely doable, but what's the minimum needed?  The target service already exists, so what objects would you need to place on the source and target service?  I think you would just require the Event Notification, route & contract (& endpoint?) on the sender, and on the receiver you'd need an endpoint, transport route, queue, and an activated SP.  Thoughts?  Thanks


Tuesday, January 10, 2012 - 5:28:18 AM - Arshad Back To Top

Yes you are absolutely right Aaron,

You might also like to have look on extended events features of SQL Server 2008 and later versions which provide more granular level control on tracing :

http://www.mssqltips.com/sqlservertip/2144/an-overview-of-extended-events-in-sql-server-2008/


Monday, January 09, 2012 - 10:47:27 PM - Aaron Bertrand Back To Top

I know it's been 13 months but I just wanted to mention that event notifications and notification services are two very different concepts. While the latter is deprecated the former is certainly not.


Wednesday, October 20, 2010 - 1:40:24 PM - Manoj Back To Top
I am trying to implement a feature in SQL Server so that when a new login is created in SQL Server 2005, it should raise an event so that the client application can add the newly created login info to another custom table.

I recently read about deprecation of SQL Server Notification Services in SQL Server 2008. Although I am not very sure that if that was the right solution for this problem.

Does anyone know what would be a correct solution for such a problem? Which feature to use to send a notification to another program that a new login has been created in SQL Server 2005.


Saturday, October 09, 2010 - 2:42:06 AM - Arshad Back To Top
Hi Amin,

Event notification is a feature based on SQL Server Service Broker and available even in SQL Server 2008 and SQL Server 2008 R2 as well. Hope it helps. Please let me know if you have any doubts.


Friday, October 08, 2010 - 6:12:44 PM - Amin Mesbahi Back To Top
Thanks for your article but SSNS (SQL Server Notification Services) is a discontinued feature since SQL Server 2008...

This feature is useful only in SQL Server 2005 not lower versions not highr versions!


Monday, September 27, 2010 - 9:35:59 AM - Ranga Back To Top
Good article, very simple and precise.


Learn more about SQL Server tools