An Extended Event script

I have been enjoying XE alot recently ( from following SQLSKILLS) so I decided to write something that I used recently – aim: find what process within a specific database that was causing ALTER / CREATE commands, again I am modifying the below for a test concept.

This is what I used:


CREATE EVENT SESSION [XE_tracker] ON SERVER
ADD EVENT  sqlserver.object_altered
(
    ACTION (sqlserver.database_id)
    WHERE  (sqlserver.database_id = 5) -- bound to my DB of interest
),

ADD EVENT  sqlserver.object_created
(
    ACTION (sqlserver.database_id)
    WHERE  (sqlserver.database_id = 5)
),
ADD EVENT sqlserver.sql_statement_completed(
     ACTION (sqlserver.database_id, sqlserver.plan_handle,
            sqlserver.session_id, sqlserver.sql_text)
			WHERE  (sqlserver.database_id = 5) 

			)
add target package0.asynchronous_file_target	-- Store events on disk
(
	set filename           = N'C:\sqlserver\tracker.xel',
		max_file_size      = 1024, /* MB */
		max_rollover_files = 4
)
go

Then I could deduce what was happening – below showing ALTER command.

CaptureEE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s