Monitoring using EVENTDATA() Function

One of most under utilized function by DBA for monitoring SQL server is EVENTDATA() function, With Evendata function you can monitor many database and instance level changes at a granular level.

The only drawback is Eventdata functions can be used only within a DDL trigger or Logon trigger.

Let us see some examples how we can use Evendata() function,

I am going to use DDL_SERVER_LEVEL_EVENTS and DDL_DATABASE_LEVEL_EVENTS event group in a trigger to capture some simple DDL events at server level and database level,

1. DDL_Server_LEVEL_Events:-

CREATE TRIGGER Eventtest

ON ALL SERVER

FOR DDL_SERVER_LEVEL_EVENTS

AS

DECLARE @result XML

SET @result = EVENTDATA()

SELECT

GETDATE() As Time,

@result.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(20)') As EventType,

@result.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(20)') As SPID,

@result.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(20)') As LoginName,

@result.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(300)') As TSQL,

@result.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(20)') As ServerName,

@result.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(20)') As DatabaseName

GO

CREATE DATABASE TEST

GO

ALTER DATABASE TEST SET RECOVERY SIMPLE

GO

CREATE LOGIN [Test] WITH PASSWORD='test',CHECK_POLICY=OFF

GO

DROP LOGIN [Test]

GO

DROP DATABASE TEST

GO

DROP TRIGGER Eventtest

ON ALL SERVER;

GO

Result:-

Time
EventType
SPID
LoginName
TSQL
ServerName
DatabaseName
2012-08-20 17:45:54.927
CREATE_DATABASE
135
Test\Vinoth
CREATE DATABASE TEST   
T01\TEST
TEST
2012-08-20 17:45:54.997
ALTER_DATABASE
135
Test\Vinoth
ALTER DATABASE TEST SET RECOVERY SIMPLE  
T01\TEST
TEST
2012-08-20 17:45:55.040
CREATE_LOGIN
135
Test\Vinoth
CREATE LOGIN [Test] WITH PASSWORD='******',CHECK_POLICY=OFF  
T01\TEST
NULL
2012-08-20 17:45:55.097
DROP_LOGIN
135
Test\Vinoth
DROP LOGIN [Test]  
T01\TEST
NULL
2012-08-20 17:45:55.203
DROP_DATABASE
135
Test\Vinoth
DROP DATABASE TEST  
T01\TEST
TEST

2. DDL_DATABASE_LEVEL_EVENTS:-

CREATE TRIGGER Eventtest

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @result XML

SET @result = EVENTDATA()

SELECT

GETDATE() As Time,

@result.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(20)') As EventType,

@result.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(20)') As SPID,

@result.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(20)') As LoginName,

@result.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(300)') As TSQL,

@result.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(20)') As ServerName,

@result.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(20)') As DatabaseName

GO

CREATE TABLE test1 (col1 int)

GO

ALTER TABLE test1 ADD col2 int

GO

CREATE LOGIN [Test] WITH PASSWORD='test',CHECK_POLICY=OFF

GO

CREATE USER test for login Test

GO

GRANT SELECT ON test1 TO test

GO

DROP DATABASE TEST

GO

DROP TRIGGER Eventtest

ON DATABASE;

GO

Result:-

You can see all server events are ignored and only database events are captured.

Time
EventType
SPID
LoginName
TSQL
ServerName
DatabaseName
2012-08-20 17:59:09.087
CREATE_TABLE
135
Test\Vinoth
CREATE TABLE test1 (col1 int)  
T01\TEST
TEST
2012-08-20 17:59:09.157
ALTER_TABLE
135
Test\Vinoth
ALTER TABLE test1 ADD col2 int  
T01\TEST
TEST
2012-08-20 17:59:09.197
CREATE_USER
135
Test\Vinoth
CREATE USER test for login Test  
T01\TEST
TEST
2012-08-20 17:59:09.230
GRANT_DATABASE
135
Test\Vinoth
GRANT SELECT ON test1 TO test  
T01\TEST
TEST

I have just given a sample of what can be done, the total events which can be captured using even data can be found in http://msdn.microsoft.com/en-us/library/bb510452.aspx ,You can also find all the events supported by event data C:\Program Files\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.