Michael J. Swart

SQL Server Magazine has an article this month by Andrew Kelly called Generating Server-Side Traces See how easily you can capture trace data without SQL Server Profiler. The article has four code listings (including one condensed code listing “for the sake of brevity”) After reading the article, I believe it’s not difficult, but I get the feeling that it could be easier.

This isn’t the first time I’ve struggled with server side traces. I talked about having an xml trace definition file on Microsoft’s connect site. And that in turn came from a discussion on the microsoft.public.sqlserver.server forum here.

Kelly’s article this month talks about how to export the trace definition from within Profiler by scripting the commands used to create the trace. It’s workable, but it’s not elegant. Code (the sp_trace* commands) seems to be getting mixed up with data (the trace definition) here. It would be nice to have a trace definition stored as data, not code.

Right now, in SQL Server 2008, there’s a hint of an xml trace definition, but it’s only used in the context of the SQL Server’s 2008 Data Collector. Here’s an example of what that trace definition looks like:

<ns:SqlTraceCollector xmlns:ns="DataCollectorType" use_default="0">
<Events>
  <EventType name="Security Audit">
    <Event id="14" name="Audit Login" columnslist="1,9,6,10,14,11,12" />
    <Event id="15" name="Audit Logout" columnslist="15,16,9,17,6,10,14,18,11,12,13" />
  </EventType>
  <EventType name="Sessions">
    <Event id="17" name="ExistingConnection" columnslist="1,9,6,10,14,11,12" />
  </EventType>
  <EventType name="Stored Procedures">
    <Event id="10" name="RPC:Completed" columnslist="15,16,9,17,2,10,18,11,12,13,6,14" />
  </EventType>
  <EventType name="TSQL">
    <Event id="12" name="SQL:BatchCompleted" columnslist="15,16,1,9,17,6,10,14,18,11,12,13" />
    <Event id="13" name="SQL:BatchStarting" columnslist="1,9,6,10,14,11,12" />
  </EventType>
</Events>
<Filters>
  <Filter columnid="10" columnname="ApplicationName" logical_operator="AND" comparison_operator="NOTLIKE" value="SQL Server Profiler - de0305e7-9fbf-4a52-8487-2aab91a78d76" />
</Filters>
</ns:SqlTraceCollector>

You can get at these trace definitions by exporting from Profiler’s File menu as shown here:

You can use this trace definition with a custom procedure or app to build your trace. So far this is not one bit easier than the standard way described in SQL Magazine this month. In fact it’s a lot harder.

What Would Be Nice:
I’ve got a few ideas that would make things easier:

1. Allow Profiler to export trace definition files in XML format
2. Extend the system table sys.traces to include an xml field containing the trace definition.
3. Create a new stored procedure (maybe called sp_trace_ApplyDefinition) which takes a trace definition and a trace handle and applies events and filters to it.
4. Extend the trace definition to include extra trace information like tracefile, maxfilesize, stoptime, filecount etc…

If I had everything on my Christmas list, then Profiler trace management would be so much easier. Andrew Kelly’s article would have been shorter.

Visit Microsoft’s connect site here to vote for this suggestion.

Cheers,