another lesson learned last week.
I pulled a very long trace (more than 80 trc files, about 30GB of data) from a SQL Server 2008 x64 with the modified PerfStatScript2008 Christian Bolton mentioned in the online chapter of the “Professional SQL Server 2008 – Internals and Troubleshooting” (by the way: I really can recommend this book and no, I get no money for advertising it). Before you start to ask why I took so much data: I wanted to analyze a special process of a custom program from the beginning to the end with much information as possible.
Then I´ve moved the trace files to another machine (this time an SQL 2008 R2 x64) and started SQLNexus. Once SQLNexus had finished the import I ran the first report and was wondering why only the first two trace files got imported. The ReadTrace.log told me that only the first two trace files where imported. Strange thing. When opened the trace file where ReadTrace stopped with, suddenly an error came up: “Failed to read file data. File may be corrupt or it may have a wrong data format.”
Uh-Oh…So I placed the error SQL Profiler raised in a tweet and got the hint to use ClearTrace.
I installed ClearTrace on a client PC and could import all trace files without any errors. So what was the difference now? To quote David Levy: ”Not sure why it works when profiler won’t (I thought they used the same libraries) but it works.”
And then I got it: on the client PC I had the SSMS 2008 while the server I wanted to use for analyzing the data was an R2 installation. Re-run the whole SQLNexus import from the client PC now worked as well.
I do not have the answer right now why the SQL Server R2 Profiler showed up this little problem. Maybe one of you has already an explanation for this, so post it in the comments, send me an email or use twitter (@Feleryan)
By now, if I have to analyze SQL 2008 trace data, I will use SSMS 2008 and thanks to @Dave_Levy for the good advice regarding ClearTrace.