workaround for SQLNexus fail to import SQL 2016 trc file (SQLNexus ReadTrace匯入sql 2016 trc失敗)

workaround for SQLNexus fail to import SQL 2016 trc file
(SQLNexus ReadTrace匯入sql 2016 trc失敗)

Problem:

Readtrace (SQL Profiler TRC Files) Import failed.

Analysis:

from ReadTrace.log

07/23/18 00:32:50.485 [0X00001364] The major version number (13) in the trace file header is not a supported file version.
07/23/18 00:32:50.485 [0X00001364] ERROR: Read of file header for file C:\Temp\20180712110000\SQLSERVER-1_SQLDIAG__sp_trace.trc failed with operating system error 0x8007000D (The data is invalid)
07/23/18 00:32:50.519 [0X00001364] *** ERROR: Attempt to initialize trace file reader failed with operating system error 0x8007000D (The data is invalid)
07/23/18 00:32:50.519 [0X00001364] Reads completed - Global Error Status 0xfffffffe
07/23/18 00:32:50.520 [0X00001364] Signaling worker threads to complete final actions.

Possible Cause原因:

目前的版本RML ReadTrace無法匯入SQL Server 2016 trc

Workaround:

執行以下Powershell，將F:\Temp\20180707192503目錄下的trc檔變更版本號碼，改成舊版SQL Server

# 目前的版本RML ReadTrace無法匯入SQL Server 2016 trc
# Workaround: Change the version in trc file

Write-Output "Change the version in trc file"
Get-Date

# 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

$OutputPath = "F:\Temp\20180707192503"
Get-ChildItem $OutputPath -Filter *.trc| 
Foreach-Object {
 $trc = "$OutputPath\$_"
 [System.IO.FileMode] $open = [System.IO.FileMode]::OpenOrCreate
 $stream = New-Object System.IO.FileStream -ArgumentList $trc, $open
 $stream.Seek($offset, [System.IO.SeekOrigin]::Begin);
 $stream.Write($versionData, 0, $versionData.Length);
 $stream.Close()

Write-Output "Change Done $trc"
}