Convert Existing SQL Server Traces to Extended Events Sessions

By:   |   Comments   |   Related: > Extended Events


While we don't know exactly when SQL Trace will stop working, we do know that it is deprecated and that, at some point, you will need to switch over to Extended Events. Arshad Ali has already provided a great tip on Getting Started with Extended Events, but if you have a lot of traces in your environment, changing them all to Extended Events session by hand is going to be a nightmare.


Microsoft has an article that sounds promising, entitled, "Convert an Existing SQL Trace Script to an Extended Events Session." But if you look at step 4, you can see that, beyond discovery, this is still going to be a largely manual process:

  • 4. Manually create an Extended Events session ...

Thankfully, Jonathan Kehayias of SQLskills has created a procedure that can automate this process for you, as long as you are on SQL Server 2012 or newer. (In SQL Server 2008 and 2008 R2, Extended Events did not have anywhere near full parity with SQL Trace, so it is unlikely that many of your traces could be directly converted.) I'm not going to repeat the procedure code here, since it may change over time; you can grab the current version directly from his blog post:

I implemented this procedure on my system, then used the example query to generate an Extended Events session that was roughly equivalent to the default trace:

EXEC master.dbo.sp_SQLskills_ConvertTraceToExtendedEvents 
		@TraceID     = 1, 
		@SessionName = 'XE_Default_Trace', 
		@PrintOutput = 1, 
		@Execute     = 1;

This creates the Extended Events session, but it doesn't actually start it. You need to add the following:


(For the default trace, you'll notice that several server-level events, like changing a database owner, still do not have an Extended Events equivalent. So for some of those events, you'll need to find other ways to capture them - such as SQL Server Audit. Admittedly, Extended Events was not intended to be a direct 1-for-1 replacement for SQL Trace, but it should come pretty close in a lot of cases.)

Now, I wanted to see that the new Extended Events session I created was capturing the same kind of data as the default trace, so I ran this batch for sanity checking:

PRINT 'Testing object_created.';
EXEC sp_executesql N'CREATE TABLE dbo.whatever(id INT);';
RAISERROR('testing errorlog_written event', 16, 1) WITH LOG;

Then I searched both the default trace file and the session target file for those events:


  @path NVARCHAR(260),

          CHARINDEX(N'\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;

  StartTime = DATEADD(MINUTE, @utcOffset, StartTime), 
FROM sys.fn_trace_gettable(@path, DEFAULT)
  (EventClass = 46 -- Object:Created
   AND EventSubClass = 1 -- ddl_phase: commit
   AND ObjectName = N'whatever')
  (TextData LIKE N'%errorlog'+N'_written%');

DECLARE @xepath NVARCHAR(260);
SET @xepath = N'XE_Default_Trace*.xel';

  StartTime = d.value(N'(/event/@timestamp)[1]', N'datetime'),
  ObjectName = d.value(N'(/event/data[@name="object_name"]/value)[1]', N'nvarchar(128)'),
  TextData = d.value(N'(/event/data[@name="message"]/value)[1]', N'varchar(max)')
  SELECT CONVERT(XML, event_data) 
    FROM sys.fn_xe_file_target_read_file(@xepath, NULL, NULL, NULL)
    WHERE object_name IN (N'object_created', N'errorlog_written')
) AS x(d)
  d.value(N'(/event/data[@name="object_name"]/value)[1]', N'nvarchar(128)') = N'whatever'
  AND d.value(N'(/event/data[@name="ddl_phase"]/value)[1]', N'int') = 1  -- commit
OR d.value(N'(/event/@name)[1]', N'nvarchar(128)') = N'errorlog_written';

And sure enough, I found them both, though what they stored was slightly different - the default trace captured the actual command that was issued to SQL Server, while the Extended Events session reported what was ultimately placed in SQL Server's error log (and truncated it):

Events captured by default trace and equivalent Extended Events session

I will admit that querying fn_xe_file_target_read_file can be a bit more tedious than dealing with the output of fn_trace_gettable, and it can be a lot slower (a lot of people will dump the XML output into a separate #temp table before running XQuery against it). Also, I could have used Profiler and "Watch Live Data" to look at the trace and session data in real time, but you shouldn't really be getting into that habit, because it will inevitably translate to usage in production scenarios, and that could be disastrous.

It is important to note that whether you continue using SQL Trace or you convert to Extended Events, there will always be some impact to the monitored server, often called "the observer effect." In a different article, Jonathan demonstrated that an Extended Events session had less observer impact than an equivalent server-side trace, so in addition to the deprecation aspect, performance can also be a motivational factor to move from trace to Extended Events.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms