SQL Server 2016 readtrace rml tool

▲
2
▼

I have captured a trace file in a SQL Server 2016 profiler and wanted to use the readtrace and reporter to visualize the SQL summaries. I can process trace files captured in SQL Server 2008 in that RML installation. However, when I try to process the 2016 trace files I get the following error:

01/31/17 08:45:11.227 [0X00004628] Attempting DOD5015 removal of [c:\temp\traces\trc2_out\ReadTrace.log]
01/31/17 08:45:11.231 [0X00004638] I/O Completion manager started
01/31/17 08:45:11.256 [0X00004628] Readtrace a SQL Server trace processing utility.
Version 9.04.0051 built for x64.
Copyright ⌐ 1997-2014 Microsoft. All Rights Reserved
01/31/17 08:45:11.259 [0X00004628]             Computer: XXX
01/31/17 08:45:11.260 [0X00004628]          Base Module: C:\Program Files\Microsoft Corporation\RMLUtils\ReadTrace.exe
01/31/17 08:45:11.261 [0X00004628]           Process Id: 17956
01/31/17 08:45:11.262 [0X00004628]  Active proc mask(0): 0x00000001
01/31/17 08:45:11.263 [0X00004628]         Architecture: 9
01/31/17 08:45:11.264 [0X00004628]            Page size: 4096
01/31/17 08:45:11.265 [0X00004628]                 CPUs: 1
01/31/17 08:45:11.265 [0X00004628]     Processor groups: 1
01/31/17 08:45:11.266 [0X00004628]         Highest node: 0
01/31/17 08:45:11.269 [0X00004628]   Proximity: 00  Node: 00
01/31/17 08:45:11.270 [0X00004628] ---------------------------------------
01/31/17 08:45:11.271 [0X00004628]                Group: 0
01/31/17 08:45:11.272 [0X00004628] ---------------------------------------
01/31/17 08:45:11.273 [0X00004628]         Package mask: 0x00000001
01/31/17 08:45:11.273 [0X00004628]         Processor(s): 0x00000001 Function units: Separated
01/31/17 08:45:11.274 [0X00004628]         Processor(s): 0x00000001 assigned to Numa node: 0
01/31/17 08:45:11.275 [0X00004628] Current time bias: -60 minutes -1.00 hours DST Standard
01/31/17 08:45:11.276 [0X00004628] -Itrc2c.trc
01/31/17 08:45:11.277 [0X00004628] -otrc2_out
01/31/17 08:45:11.280 [0X00004628] -SXXX\XXX
01/31/17 08:45:11.318 [0X00004628] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x0006020E and Defined: 0x0006020E
01/31/17 08:45:11.321 [0X00004628] Attempting to cleanup existing RML files from previous execution
01/31/17 08:45:11.322 [0X00004628] Using extended RowsetFastload synchronization
01/31/17 08:45:11.323 [0X00004628] Establishing initial database connection
01/31/17 08:45:11.323 [0X00004628] Server: XXX\XXX
01/31/17 08:45:11.324 [0X00004628] Database: PerfAnalysis
01/31/17 08:45:11.325 [0X00004628] Authentication: Windows
01/31/17 08:45:11.385 [0X00004628] Using SQLOLEDB version 11.0.6518.0
01/31/17 08:45:11.394 [0X00004628] Connected to SQL Server Version, Major: 13, Minor: 0, Build: 4001
01/31/17 08:45:11.395 [0X00004628] Creating or clearing the performance database
01/31/17 08:45:11.759 [0X00004628] The major version number (13) in the trace file header is not a supported file version.
01/31/17 08:45:11.763 [0X00004628] ERROR: Read of file header for file c:\temp\traces\trc2c.trc failed with operating system error 0x8007000D (The data is invalid)

It seems that trace file has an incorrect format that can not be processed by readtrace. Is there a solution for this? Is there some other way how to get statistical overview about my trace file? I know that trace files are deprecated and extended events should be used instead. Extended events are not an option right now.

sql-server
trace
asked on Stack Overflow Jan 31, 2017 by Radim Bača • edited Jan 31, 2017 by marc_s

2 Answers

▲
1
▼

From https://github.com/Microsoft/SqlNexus/issues/49

suresh-kandoth commented on Jun 3 To get a version of ReadTrace that can process SQL Server 2016 and 2017 trace files or XEL files, follow these steps:

Download the Database Experimentation Assistant (DEA) from https://www.microsoft.com/en-us/download/details.aspx?id=54090 Install the DEA toolset. Default path used is "C:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant" If you look in the folder "C:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant\Dependencies\X64" you will notice the latest versions of ReadTrace. Make a backup copy of your existing ReadTrace and other related files from C:\Program Files\Microsoft Corporation\RMLUtils Copy the 4 files from "C:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant\Dependencies\X64" to "C:\Program Files\Microsoft Corporation\RMLUtils" and overwrite the existing files. Now you should able to process the trace files of SQL Server 2016 and 2017 using the newer version of ReadTrace. The team that owns the ReadTrace utilities has an action item to update/refresh the download bits for RML with these new binaries. There is no estimated date on when this will be made available.

answered on Stack Overflow Aug 31, 2018 by Bill
▲
0
▼

The header of the trace file needs to be modified for ReadTrace to process it. I use the powershell code found here. 

## =============================================
## Author:      Gianluca Sartori - @spaghettidba
## Create date: 2012-11-07
## Description: Changes the version information
##              in the header of a SQL Server trace
## =============================================
cls

# Enter your filename here
$fileName = "somefile.trc"

# The version information we want to write: 0x0A = 10 = SQLServer 2008
[Byte[]] $versionData = 0x0A
# The offset of the version information in the file
$offset = 390

[System.IO.FileMode] $open = [System.IO.FileMode]::OpenOrCreate
$stream = New-Object System.IO.FileStream -ArgumentList $fileName, $open
$stream.Seek($offset, [System.IO.SeekOrigin]::Begin);
$stream.Write($versionData, 0, $versionData.Length);
$stream.Close()
answered on Stack Overflow Mar 25, 2017 by damien • edited Mar 25, 2017 by Alexei - check Codidact

User contributions licensed under CC BY-SA 3.0