Ampelofilosofies

homeaboutrss
The mind is like a parachute. If it doesn't open, you're meat.

SQLServer profiling

05 Mar 2010

I’ve been trying to track down a deadlock problem that has been plaguing our system for quite a while now. To this end we’ve run a trace on the production server (on the events to track and how to analyze them read “this”:http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/ ). Unfortunately, I ended up with 386 trace files. Loading them in SQLProfiler is a sure path to RSI. Thankfully google is your friend and can find everything in MS’s knowledge base :). This “article”:http://support.microsoft.com/kb/270599 describes how to programmatically load trace files into trace tables. It’s for SQLServer 2000, but the relevant SQL still works on SQLServer 2008.

In short, I created a new database (Traces) and run the following query:

USE Traces
GO
SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\trace_file.trc',default)

This will create the file trace_table and load all the trace files (that’s what the default in the function call does). It took quite a while, but at the end it was a lot better than hitting enter 386 times in SQLProfiler (and that every time I tried to filter events)

blog comments powered by Disqus