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
Thanks to Mike for providing the class file in this blog