Learning the Tricks of the Trade - Microsoft SQL Server 2008 R2 Administration - page 417

2. Using SSMS open a new query window and execute the following statements:
--CREATE XEVENT session to ETW File target
CREATE EVENT SESSION LongRunningQueryExecution_ETW ON SERVER
ADD EVENT sqlserver.sp_statement_completed
( ACTION (sqlserver.sql_text)
WHERE sqlserver.database_id = 5 AND duration > 30000) ,
ADD EVENT sqlserver.error_reported (
ACTION (package0.callstack, sqlserver.session_id, sqlserver.
sql_text, sqlserver.tsql_stack)
WHERE ([severity]>=(20)
OR ([error]=(17803)
OR [error]=(701)
OR [error]=(802)
OR [error]=(8645)
OR [error]=(8651)
OR [error]=(8657)
OR [error]=(8902))) ),
ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_
recorded
add target package0.ETW_CLASSIC_SYNC_TARGET
(SET DEFAULT_ETW_SESSION_LOGFILE_PATH=N'D:\ENTZ\FS\ETW\SQLETW_
LongRunningQueries.etl')

3. From Start | All Programs | Accessories | right-click on the Command Prompt and
choose the Run As Administrator to open a command prompt window to execute the
following statements:
logman start "NT Kernel Logger" -p "Windows Kernel Trace"
(disk,file) -ets -o D:\ENTZ\FS\ETW\SQLETW_LongRunningQueries.etl
-bs 1024 -ct system

4. For a full list of options on LOGMAN utility, run logman /? in the command

prompt window. Again, from the SSMS Query window, let us continue the
Extended Events procedures:
-- START EVENT SESSION
ALTER EVENT SESSION LongRunningQueryExecution_ETW ON SERVER
STATE=start;