Now let's move that lets move on to the other three Extended Event objects:

-          Actions. This Extended Objects actions get executed before events are published (stored in buffers to be transferred to the targets). Currently they are used additional data (like the TSQL Statement related to an event, the session, the user) or generate a mini dump.

-          Predicates: Predicates express are logical expressions that specify what predicates to fire (E.g. only listen to errors with a severity greater than 16). This are composed of two Extended Objects:

o   Predicate comparators: Defines an operator for a pair of values. Examples:

§  Severity > 16

§  error_message = ‘Hello World!!’

o   Predicate sources: These are values that can be also used by the predicates. They are generic data that isn’t usually provided in the event (similar to the actions).

§  Sqlserver.username = ‘Tintin’

As logical expressions they can be combined using logical operators (and, or, not).  Note: This pair always has to be first an event field or predicate source and then a value

Let’s do another small Example. We will trigger errors but we will use the ones that have severity >= 10 and the error message != ‘filter’. To verify this we will use the action sql_text that will attach the sql statement to the event data:

-- TRIES TO ELIMINATE PREVIOUS SESSIONS

BEGIN TRY

      DROP EVENT SESSION test_session ON SERVER

END TRY BEGIN CATCH END CATCH

GO

-- CREATES THE SESSION

CREATE EVENT SESSION test_session ON SERVER

ADD EVENT sqlserver.error_reported

      (ACTION (sqlserver.sql_text) WHERE severity = 2 and (not (message = 'filter')))

ADD TARGET package0.asynchronous_file_target

-- CONFIGURES THE FILE TARGET

(set filename = 'c:\temp\data2.xel' , metadatafile = 'c:\temp\data2.xem')

GO

-- STARTS THE SESSION

ALTER EVENT SESSION test_session ON SERVER STATE = START

GO

-- THIS EVENT WILL BE FILTERED BECAUSE SEVERITY != 2

RAISERROR (N'PUBLISH', 1, 1, 7, 3, N'abcde');

GO

-- THIS EVENT WILL BE FILTERED BECAUSE MESSAGE = 'FILTER'

RAISERROR (N'FILTER', 2, 1, 7, 3, N'abcde');

GO

-- THIS ERROR WILL BE PUBLISHED

RAISERROR (N'PUBLISH', 2, 1, 7, 3, N'abcde');

GO

-- STOPS LISTENING FOR THE EVENT

ALTER EVENT SESSION test_session ON SERVER

STATE = STOP

GO

-- REMOVES THE EVENT SESSION FROM THE SERVER

DROP EVENT SESSION test_session ON SERVER

GO

-- REMOVES THE EVENT SESSION FROM THE SERVER

select CAST(event_data as XML) as event_data

from sys.fn_xe_file_target_read_file

('c:\temp\data2*.xel','c:\temp\data2*.xem', null, null)

This last statement will output one event with the following data:

<event name="error_reported" package="sqlserver" id="100" version="1" timestamp="2010-03-05T23:15:05.481Z">

  <data name="error">

    <value>50000</value>

    <text />

  </data>

  <data name="severity">

    <value>2</value>

    <text />

  </data>

  <data name="state">

    <value>1</value>

    <text />

  </data>

  <data name="user_defined">

    <value>true</value>

    <text />

  </data>

  <data name="message">

    <value>PUBLISH</value>

    <text />

  </data>

  <action name="sql_text" package="sqlserver">

    <value>-- THIS ERROR WILL BE PUBLISHED

RAISERROR (N'PUBLISH', 2, 1, 7, 3, N'abcde');

</value>

    <text />

  </action>

</event>

If you see more events, check if you have deleted previous event files. If so, please run

-- Deletes previous event files

EXEC SP_CONFIGURE

GO

EXEC SP_CONFIGURE 'xp_cmdshell', 1

GO

RECONFIGURE

GO

XP_CMDSHELL 'del c:\temp\data*.xe*'

GO

or delete them manually.

More Info on Events: Extended Event Events

More Info on Targets: Extended Event Targets

More Info on Sessions: Extended Event Sessions

More Info on Actions: Extended Event Actions

More Info on Predicates: Extended Event Predicates