SQL Server - Tracking Object deleted informations using Extended Events

This article is about tracking Object deleted informations using Extended Events .


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


See Also:





No comments: