Converting a SQL Trace to an Extended Event session

So you are comfortable with SQL Trace events and suddenly you heard that it is on the deprecated list for SQL 2012.

Are you wondering if there is an easy way to convert a SQL trace file to an extended event file ?

 

Here is an easy way to convert a SQL Trace into an Extended Events file.

 

-- 1)

---Enable CLR

sp_configure'clr enabled', 1

go

 

---2)

--- Create an Assembly using the TracetoXevents.dll file provided  in this blog article

CREATE ASSEMBLY TraceToXESessionConverter

FROM 'C:\Users\srgolla\Desktop\Scratchpad\TracetoXevents.dll'

WITH PERMISSION_SET=SAFE

GO

 

-- 3)

---Create a stored procedure from the above assembly.

/* @traceid is the id of the trace you want to convert to Extended Events from select * from sys.traces

@session_name is the name of the extended events you want in Extended Events*/

 

CREATE PROCEDURE CreateEventSessionFromTrace

@trace_id int,

@session_name nvarchar(max)

AS

EXTERNAL NAME TraceToXESessionConverter.StoredProcedures.ConvertTraceToExtendedEvent

GO

　

--- 4)

--- Output of this procedure is a text containing the definition of the extended events

exec CreateEventSessionFromTrace 1,'XE_DefaultTemplateTrace'

go

 

 

You get the output as :

-- Event Session DDL autogenerated using ConvertTraceToExtendedEvent procedure.

-- (c) 2009 Microsoft Corporation.

CREATE EVENT SESSION XE_DefaultTemplateTrace

ON SERVER

ADD EVENT sqlserver.existing_connection

(ACTION (sqlserver.client_app_name, sqlserver.client_pid, sqlserver.nt_username, sqlserver.server_principal_name, sqlserver.session_id)

WHERE sqlserver.client_app_name NOT LIKE '%SQL Server Profiler - 644151b0-726e-41d8-bdda-d18cfbb10cb4%'

)

,

ADD EVENT sqlserver.login

(ACTION (sqlserver.client_app_name, sqlserver.client_pid, sqlserver.nt_username, sqlserver.server_principal_name, sqlserver.session_id)

WHERE sqlserver.client_app_name NOT LIKE '%SQL Server Profiler - 644151b0-726e-41d8-bdda-d18cfbb10cb4%'

)

 

 

 

 

References:

Mike Wachal's "Using Extended Events" blog article

https://blogs.msdn.com/b/extended_events/archive/2010/12/10/migrating-from-sql-trace-to-extended-events.aspx

Thanks to Mike for providing the class file in this blog

 

 

 

TracetoXevents.dll