Monday, 20 August 2012

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.

1 comment: