SQL Server – RML Utility – Converting Extended Events to SQL Server Profiler Trace Files

Background

Extended Events is the modern way to collect events.  But, in some cases one might want to convert Extended Events to SQL Server Profiler Traces.

RML Utility

Reading through a blog post found out one can convert files generated through Extended Events into Profiler Trace Files.

Download Site

The latest version of RML Utility are available here.

Curiosity

The saying goes curiosity killed the cat.

Same here this last Monday in January.

 

Installation

Download and Installed RML Utilities.

Review Installation

Targeted Folder

The targeted folder is C:\Program Files\Microsoft Corporation\RMLUtils

ReadTrace

File Version

Image

Explanation

The file version is 9.4.51.0

 

Processing

Code


set "_folderReadTrace=C:\Program Files\Microsoft Corporation\RMLUtils\"

set "_xelFolder=Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\replay"

set "_xelFileFirst=replay_0_131617352214050000.xel"

set "_xelFileTarget=%_xelFolder%\%_xelFileFirst%"

set "_tracefile=Z:\Microsoft\SQLServer\ProfileTrace\TraceFiles"

set "_sqlServer=DBLAB"

if not exist %_tracefile% mkdir %_tracefile%

"%_folderReadTrace%\readtrace.exe"  -S%_sqlServer% -a -I"%_xelFileTarget%" -MS -o"%_tracefile%"

 

Output

Image

Textual


01/29/18 16:29:39.892 [0X000015D8] I/O Completion manager started
01/29/18 16:29:39.893 [0X00002BE4] Attempting DOD5015 removal of [Z:\Microsoft\S
QLServer\ProfileTrace\TraceFiles\ReadTrace.log]
01/29/18 16:29:39.896 [0X00002BE4] Readtrace a SQL Server trace processing utili
ty.
Version 9.04.0051 built for x64.
Copyright ⌐ 1997-2014 Microsoft. All Rights Reserved
01/29/18 16:29:39.897 [0X00002BE4]             Computer: D-ITS-AYSOSQL02
01/29/18 16:29:39.897 [0X00002BE4]          Base Module: C:\Program Files\Micros
oft Corporation\RMLUtils\ReadTrace.exe
01/29/18 16:29:39.897 [0X00002BE4]           Process Id: 6480
01/29/18 16:29:39.898 [0X00002BE4]  Active proc mask(0): 0x0000000F
01/29/18 16:29:39.898 [0X00002BE4]         Architecture: 9
01/29/18 16:29:39.898 [0X00002BE4]            Page size: 4096
01/29/18 16:29:39.898 [0X00002BE4]                 CPUs: 4
01/29/18 16:29:39.899 [0X00002BE4]     Processor groups: 1
01/29/18 16:29:39.899 [0X00002BE4]         Highest node: 0
01/29/18 16:29:39.899 [0X00002BE4]   Proximity: 00  Node: 00
01/29/18 16:29:39.900 [0X00002BE4] ---------------------------------------
01/29/18 16:29:39.900 [0X00002BE4]                Group: 0
01/29/18 16:29:39.900 [0X00002BE4] ---------------------------------------
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000001 Function uni
ts: Separated
01/29/18 16:29:39.901 [0X00002BE4]         Package mask: 0x00000003
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000002 Function uni
ts: Separated
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000004 Function uni
ts: Separated
01/29/18 16:29:39.902 [0X00002BE4]         Package mask: 0x0000000C
01/29/18 16:29:39.902 [0X00002BE4]         Processor(s): 0x00000008 Function uni
ts: Separated
01/29/18 16:29:39.902 [0X00002BE4]         Processor(s): 0x0000000F assigned to
Numa node: 0
01/29/18 16:29:39.907 [0X00002BE4] Current time bias: 480 minutes 8.00 hours DST
 Standard
01/29/18 16:29:39.907 [0X00002BE4] -SDBLAB
01/29/18 16:29:39.908 [0X00002BE4] -a
01/29/18 16:29:39.908 [0X00002BE4] -IZ:\Microsoft\SQLServer\ExtendedEvents\Trace
Files\replay\replay_0_131617352214050000.xel
01/29/18 16:29:39.908 [0X00002BE4] File mirroring enabled, with single file targ
et
01/29/18 16:29:39.909 [0X00002BE4] -MS
01/29/18 16:29:39.909 [0X00002BE4] -oZ:\Microsoft\SQLServer\ProfileTrace\TraceFi
les
01/29/18 16:29:39.910 [0X00002BE4] Using language id (LCID): 1024 [English_Unite
d States.1252] for character formatting with NLS: 0x0006020E and Defined: 0x0006
020E
01/29/18 16:29:39.911 [0X00002BE4] Attempting to cleanup existing RML files from
 previous execution
01/29/18 16:29:39.991 [0X00002BE4] Detecting list of matching XEL files in the s
ame directory
01/29/18 16:29:40.039 [0X00002BE4]  INFO: Using additional file system cache rea
d ahead for XEL input
01/29/18 16:29:40.046 [0X00002BE4] XEL READER ERROR: The tookLock argument must
be set to false before calling this method.
01/29/18 16:29:40.048 [0X00002BE4] XEL READER ERROR: Attempt to open file Z:\Mic
rosoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XE
L failed, see previous error(s) for more details.
01/29/18 16:29:40.049 [0X00002BE4] ERROR: Attempt to open Z:\Microsoft\SQLServer
\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL failed.
01/29/18 16:29:40.050 [0X00002BE4] ERROR: Unable to construct proper base metada
ta generation for Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay
_0_131617352214050000.XEL
01/29/18 16:29:40.053 [0X00002BE4] ERROR: Attempt to determine list of .XEL file
s failed.
01/29/18 16:29:40.057 [0X00002BE4] *** ERROR: Attempt to initialize trace file r
eader failed with operating system error 0x80070006 (The handle is invalid)
01/29/18 16:29:40.061 [0X00002BE4] Reads completed - Global Error Status 0xfffff
ffe
01/29/18 16:29:40.062 [0X00002BE4] Signaling worker threads to complete final ac
tions.
01/29/18 16:29:40.067 [0X00002BE4] Waiting for the worker threads to complete fi
nal actions.
01/29/18 16:29:40.069 [0X00002BE4] Performing general cleanup actions.
01/29/18 16:29:40.072 [0X00002BE4] Performing final mirroring actions.
01/29/18 16:29:40.075 [0X00002BE4] Total Events Processed: 0
01/29/18 16:29:40.076 [0X00002BE4]  Total Events Filtered: 0
01/29/18 16:29:40.077 [0X00002BE4] =============================================
=
01/29/18 16:29:40.079 [0X00002BE4] WARNING: A significant portion of the events
where filtered.
01/29/18 16:29:40.086 [0X00002BE4]          You may want to check the filtering
criteria.
01/29/18 16:29:40.088 [0X00002BE4] =============================================
=
01/29/18 16:29:40.090 [0X00002BE4] *********************************************
**********************************
* ReadTrace encountered one or more ERRORS. An error condition typically      *
* stops processing early and the ReadTrace output may be unusable.            *
* Review the log file for details.                                            *
*******************************************************************************
01/29/18 16:29:40.094 [0X00002BE4] ***** ReadTrace exit code: -2
01/29/18 16:29:40.097 [0X00002BE4]  INFO: Max set-aside XEL event hash table ent
ry count: 0
01/29/18 16:29:40.131 [0X00002BE4]
01/29/18 16:29:40.134 [0X00002BE4] INFO: Cleaning up connection info hash table
>

Explanation

Key details:

  1. [0X00002138] Detecting list of matching XEL files in the same directory
  2. [0X00002138] INFO: Using additional file system cache read ahead for XEL input
  3. [0X00002138] XEL READER ERROR: The tookLock argument must be set to false before calling this method.
  4. [0X00002138] XEL READER ERROR: Attempt to open file Z:\Microsoft\SQLServer\ExtendedEvents\TraceFies\Replay\replay_0_131617352214050000.XEL failed, see previous error(s) for more details.
  5. ERROR: Attempt to open Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL failed.
    [0X00002138] ERROR: Unable to construct proper base metadata generation for Z:\Microsoft\SQLServe
    \ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL
  6. [0X00002138] ERROR: Attempt to determine list of .XEL files failed.
  7. [0X00002138] *** ERROR: Attempt to initialize trace file reader failed with operating system error 0x80070006 (The handle is invalid)
  8. [0X00002138] Reads completed – Global Error Status 0xfffffffe
  9. [0X00002138] ***** ReadTrace exit code: -2

 

TroubleShooting

ReadTrace

Code


"C:\Program Files\Microsoft Corporation\RMLUtils\readtrace.exe" /?

Output

Explanation

  1. -I File name of the first .TRC or .XEL file to process [REQUIRED]. Note: XEL processing is ‘BETA’ and limited.
    • Clearly states that XEL ( Extended Events ) processing is in BETA mode and limited

SysInternals

Process Monitor

Captured Events
Image

Explanation

Confirmed that the extended event file was located and opened successfully.

 

Summary

It appears that readtrace at Version 9.4.51.0, last modified on Dec 10,2014 is starting to show its age.

BTW, in SQL Server parlance 9.x.y.z is MS SQL Server v2005.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s