Note that EN is NOT the same as SQL Server Notification Services, which has been dropped by MS and was for Reporting Services.

Event Notifications started with 2005, and are woefully underutilized. Here's my annotated basic attempt at tracking all DDL changes on a server. Next up (probably next post) is creating a routable SB to a secondary server, which would have an activated SP which would write records to a table. As is, this is a proof of concept that writes to a queue. DO NOT JUST LEAVE IT RUNNING, as it'll fill up your queue, grow your database and you'll never figure out where the space is (since queues are sorta-hidden when it comes to space used)

TL;DR: Event Notifications tracks events, and routes them through service broker. This gives power and danger.

turn on service broker on the database. It's on by default in MSDB, but let's not play god in system databases today.

USE db_stuffCREATE QUEUE EventNotificationQueue GO
create the service broker queue in "db_stuff". EN uses service broker.

create SERVICE EventNotificationService ON QUEUE EventNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
The notification sends to the service which sends to the queue. It needs a schema, MS has provided that (the http).

CREATE EVENT NOTIFICATION CaptureErrorLogEvents
ON SERVER
can be server-wide, database-wide, etc. I'm tracking all DDL changes on the server, so server it is.

WITH FAN_IN
prevents multiple inserts from the same event into the same queue.

FOR DDL_EVENTS, ALTER_SERVER_CONFIGURATION
the things I'm actually tracking. you could do them individually, but there's over 1000, so I'm using "event groups". Here, I'm looking for server-level config changes, and DDL events (this includes UPDATE STATISTICS, which means it can fire a LOT)

TO SERVICE 'EventNotificationService', 'current database';
send it to the service, And yes "current database" is the proper format. Do Not Like that name.

SELECT * FROM db_stuff..EventNotificationQueue
As of the CREATE EVENT NOTIFICATION, the queue is on and catching data. Let's see what it's caught. Not really legible, is it? Let's change that.

SELECT CAST(message_body AS XML) AS message_body_xml
FROM db_stuff.dbo.EventNotificationQueue

So at this point, we'll use a CTE (or you could use a derived table; whatever) to provide a useful query that pulls out most of the provided fields.
with MESSAGE_DATA as (SELECT queuing_order, CAST(message_body AS XML) AS message_body_xml
FROM db_stuff.dbo.EventNotificationQueue
)
SELECT
message_body_xml.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ) as EventType,
message_body_xml.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') AS PostTime,
message_body_xml.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(128)') AS SPID, message_body_xml.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' ) AS LoginName,
message_body_xml.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(128)' ) AS UserName,
message_body_xml.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)' ) AS ServerName,
message_body_xml.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)' ) AS DatabaseName,
message_body_xml.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(128)' ) AS SchemaName,
message_body_xml.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)' ) AS ObjectName,
message_body_xml.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(128)' ) AS ObjectType,
message_body_xml.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)' ) AS CommandText,
*
from message_data order by queuing_order