Question On SQL Server Extended Event.

Question

Following set of code is working with SQL SERVER 2012 Standard Edition and the same set of Code is not Working with SQL SERVER 2008 R2 Standard edition.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Test_Event_Session')
    DROP EVENT session Test_Event_Session ON SERVER;
GO
CREATE EVENT SESSION [Test_Event_Session] ON SERVER
ADD EVENT sqlserver.sql_batch_completed (
 ACTION(package0.collect_system_time,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
 WHERE ((([sqlserver].[database_name]=N'master') AND ([sqlserver].[like_i_sql_unicode_string]([batch_text], N'%ALTER EVENT%')))
 OR (([sqlserver].[database_name]=N'TEST_200') AND ([sqlserver].[like_i_sql_unicode_string]([batch_text], N'%TRUNCATE%')))
 OR (([sqlserver].[database_name]=N'TEST_200') AND ([sqlserver].[like_i_sql_unicode_string]([batch_text], N'%DELETE%')))
 OR (([sqlserver].[database_name]=N'TEST_200') AND ([sqlserver].[like_i_sql_unicode_string]([batch_text], N'%UPDATE%')))
 OR (([sqlserver].[database_name]=N'TEST_200') AND ([sqlserver].[like_i_sql_unicode_string]([batch_text], N'%DROP%')))
 OR (([sqlserver].[database_name]=N'TEST_200') AND ([sqlserver].[like_i_sql_unicode_string]([batch_text], N'%ALTER%'))))),
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ((((([sqlserver].[database_name]=N'TEST_200') AND
 (([sqlserver].[like_i_sql_unicode_string]([statement],N'%TRUNCATE%'))
 OR ([sqlserver].[like_i_sql_unicode_string]([statement],N'%DELETE%')))
 OR ([sqlserver].[like_i_sql_unicode_string]([statement],N'%UPDATE%')))
 OR ([sqlserver].[like_i_sql_unicode_string]([statement],N'%DROP%')))
 OR ([sqlserver].[like_i_sql_unicode_string]([statement],N'%ALTER%')))))
ADD TARGET package0.event_file(SET filename=N'C:\Demo\Traces\Ranbir_200.xel',max_file_size=(10),max_rollover_files=(3))
GO

Reason as per my observation.

1) sqlserver.sql_batch_completed event is not available in 2008 R2 STD. ED.

2)SQL 2008 R2 STD don't have the predicate source "sql_text"

3)For output don't have event_file.

Answer 1

In 2012, we have got more addition to Extended events like Recompiling, Blocked process report etc. So as you said, sql_batch_completed event is not in 2008.

SQL_TEXT action is there, this is not an issue. Also you can try to use "sql_statement_completed".

---

Srinivasan

Answer 2

...

1) sqlserver.sql_batch_completed event is not available in 2008 R2 STD. ED.

2)SQL 2008 R2 STD don't have the predicate source "sql_text"

3)For output don't have event_file.

1 and 2 have been answered already. It's not there, right.

3)

Of course there also was a "file target", it just was called "asynchronous file target" back then. Code from SQL 2008 is UPwards compatible, but SQL 2012 not DOWNwards compatible - as mostly.Also the "file target" needed a metadata file specified

like this:

ADD TARGET package0.asynchronous_file_target(
     SET filename='C:\SQL_Analysis\XE_Tracing\SQL_XEvent_QueryRuntimes.xel'
     , max_file_size=20, max_rollover_files=120
     , metadatafile='C:\SQL_Analysis\XE_Tracing\SQL_XEvent_QueryRuntimes.xem')

---

Andreas Wolter | Microsoft Certified Master SQL Server
Blog: www.insidesql.org/blogs/andreaswolter
Web: www.andreas-wolter.com

Answer 3

I need to filter out all delete, update, truncate, alter and drop command if executed on table, but at the same time don't want the select or insert to be in the log.

Answer 4

If the first 2 points are correct then there is no need for the output file.