This article is about tracking Object deleted informations using Extended Events .
Once you start the Session , you can notice in the path location "D:\ExtendedEvents" target file will be created .
The reason for target filename getting appended with extra integer value is as follows (as stated here ) :
If you click on event_data_XML column , you will get XML output in the below format :
you will notice two rows this is because "Occurs when an object was deleted by the DROP statement. This event is raised two times for every DROP operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object."
we can shred the XML using Xquery & nodes() method to get the required information .
USE AdventureWorks2012 GO --Create sample table to test object_deleted event CREATE TABLE TEST_EE_object_deleted (Id INT) GO
--Create Session to track object_deleted event with below mentioned Actions CREATE EVENT SESSION Who_deletedObject ON SERVER ADD EVENT sqlserver.object_deleted ( ACTION (sqlserver.database_id, sqlserver.server_instance_name, sqlserver.transaction_id , sqlserver.session_id , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.sql_text ) ) --Below mentioned path location will hold the captured informations by the above created Session ADD TARGET package0.event_file( SET filename='D:\ExtendedEvents\Who_deletedObject.xel') GO --Start the Session ALTER EVENT SESSION Who_deletedObject ON SERVER STATE=START GO
Once you start the Session , you can notice in the path location "D:\ExtendedEvents" target file will be created .
The reason for target filename getting appended with extra integer value is as follows (as stated here ) :
The first time that an event file target is created, the filename you specify
is appended with _0_ and a long integer value. The integer value is calculated
as the number of milliseconds between January 1, 1600 and the date and time the
file is created. Subsequent rollover files also use this format. From examining
the value of the long integer, you can determine the most current file. The
following example illustrates how files are named in a scenario where you
specify the filename option as C:\OutputFiles\MyOutput.xel:
- first file created - C:\OutputFiles\MyOutput_0_128500310259380000.xel
- first rollover file - C:\OutputFiles\MyOutput_0_128505831770890000.xel
- second rollover file - C:\OutputFiles\MyOutput_0_132410772966237000.xel
--Drop the object DROP TABLE TEST_EE_object_deleted --Read the target file SELECT *, CAST(event_data AS XML) AS 'event_data_XML' FROM sys.fn_xe_file_target_read_file('D:\ExtendedEvents\Who_deletedObject_0_130262466127690000.xel', NULL, NULL, NULL) GO
If you click on event_data_XML column , you will get XML output in the below format :
<event name="object_deleted" package="sqlserver" timestamp="2013-10-14T17:51:18.683Z"> <data name="database_id"> <value>5</value> </data> <data name="object_id"> <value>1935345959</value> </data> <data name="object_type"> <value>8277</value> <text>USRTAB</text> </data> <data name="index_id"> <value>0</value> </data> <data name="related_object_id"> <value>0</value> </data> <data name="ddl_phase"> <value>0</value> <text>Begin</text> </data> <data name="transaction_id"> <value>94170</value> </data> <data name="object_name"> <value>TEST_EE_object_deleted</value> </data> <data name="database_name"> <value /> </data> <action name="sql_text" package="sqlserver"> <value>DROP TABLE TEST_EE_object_deleted</value> </action> <action name="server_principal_name" package="sqlserver"> <value>sa</value> </action> <action name="nt_username" package="sqlserver"> <value /> </action> <action name="session_id" package="sqlserver"> <value>52</value> </action> <action name="transaction_id" package="sqlserver"> <value>94170</value> </action> <action name="server_instance_name" package="sqlserver"> <value>SATHYA-PC</value> </action> <action name="database_id" package="sqlserver"> <value>5</value> </action> </event>
you will notice two rows this is because "Occurs when an object was deleted by the DROP statement. This event is raised two times for every DROP operation. The event is raised when the operation begins and when the operation is either rolled back or committed. Add the nt_username or server_principal_name actions to this event to determine who altered the object."
SELECT description FROM sys.dm_xe_objects WHERE name = 'object_deleted'
we can shred the XML using Xquery & nodes() method to get the required information .
ALTER EVENT SESSION Who_deletedObject ON SERVER STATE=STOP GO DROP EVENT SESSION Who_deletedObject ON SERVER GO
No comments:
Post a Comment