Trace file - how to use it

Question

Hello all,

we have an application running on an IIS 6/ASP.NET 2.0 backed by a SQL Server 2005 STD edition X64. From time to time, the application crashes with some silly messages (some of the fileds are not found into a "select firled1, filed2.. from mytable"). I obtained a trace of the activity from the sql server taken while the application reported the errors. Note: the crash is encountered only during some heavy load on that server, like creating some reports simmultaneously by several users.

The question is: how can I use the trace file to solve the situation? How can I detect what goes wrong?

Thanks

Answer 1

Hi,

OK, you need to get the trace data into a form that you can interrogate, i.e. a table.

Here are the details on how to load a trace file into a SQL Server table.

http://support.microsoft.com/kb/270599

You then need to identify the events that are responsible for your issue. Search the trace for events that occurred within the database that you are interested in and that also occurred around the time of the error you experienced.

The following link provides a good starting point for SQL Server Profiler/Trace information.

http://msdn.microsoft.com/en-us/library/ms187929.aspx

Have a read through this and if you get stuck let me know.

Cheers,

Answer 2

I've got a video tutorial on getting started with Profiler at SQLServerPedia. In a nutshell, you'll want to export that trace file into a table, and then step through it in order looking at the errors that popped up. Profiler's trace files or trace tables by themselves won't say, "Here's what you need to fix in order to avoid this error" any more than a dump file will tell you "Here's the bad line of code" - you'll still need an experienced DBA to interpret the results. You may want to take the trace file to your local SQL Server User Group meeting and see if someone can help you.