[SQL]ReadTrace遇到SQL 2012的解決方式

2013-07-23

1992
0
SQL

ReadTrace目前不Support SQL Server 2012的Trace File要怎麼辦呢?
以下透過Power Shell來將SQL 2012的Trace File改成SQL 2008的Trace File。

ReadTrace目前使用SQL 2008的Client，所以如果要使用的話，需要裝Microsoft® SQL Server® 2008 R2 SP2 功能套件。

可參考:RML Utilities and SQL Server 2012。

當我裝了之後，又遇到另一個問題!

什麼!不Support SQL 2012的Trace File。訊息如下，

The major version number (11) in the trace file header is not a supported file version.
At this time only Microsoft SQL Server 2000, 2005 and 2008 trace files are supported. The current trace version (11) is not supported.
ERROR: Read of file header for file E:\GSS\TechInfo\SQL\4.Trace\5.ReadTrace\ReadTrace.trc failed with operating system error 0x8007000D (The data is invalid)
*** ERROR: Attempt to initialize trace file reader failed with operating system error 0x8007000D (The data is invalid)
Reads completed - Global Error Status 0xfffffffe
Shutting down the worker thread message queues.
Waiting for the worker threads to complete final actions.

後來找到網路上的高手，發現可以將SQL 2012的Trace File改成2008的格式(真是利害!)

請參考:More on converting Trace Files

Copy他提供的Power shell script，修改一下$fileName變數成我SQL 2012的Trace File Path，然後執行它，如下，

## =============================================
## Author:      Gianluca Sartori - @spaghettidba
## Create date: 2012-11-07
## Description: Changes the version information
##              in the header of a SQL Server trace
## http://spaghettidba.com/2012/11/08/more-on-converting-trace-files/
## =============================================
cls
 
# Enter your filename here
$fileName = "h:\ReadTrace.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()