Share via

Jump on the Express Trace if you can – SQL Server Express server side tracing

  • Article

Running SQL Server Traces on SQL Server Express

SQL Server Express does not come with a SQL Server Profiler GUI. (Period) Though can still use SQL Server Profiler tools from other editions to connect to the SQL Server Express edition. (But make sure that you do have a valid license for that.)

(I appreciate all the work done by many community members developing a SQL Server Profiler Trace tool for SQL Server Express which can be found here. But in some cases you cannot use any external tools due to a customer policy and want to track the issue down anyway. That is where the server side tracing approach kicks in.)

First of all, do not separate the SQL Server profiler traces from the server side trace, they are both the same. What SQL Server profiler simply does is that it initiates the server side trace and uses the API to consume the trace data from it. A marvelous explanation can be found in the “SQL Server 2008 Internals” book from Kalen Delaney which describes all the providers available to consume data. In that case one tracing this down to a table should be the best for you.

If you already had a look at the stored procedure being responsible for the tracing, you might have already surrendered. Many parameters and ways to configure it, but if you have a SQL Server profiler GUI at hand (maybe from a developer edition) you catch the script by starting a second profiler tracing the commands from the first profiler. You will see event like:

exec sp_trace_create @p1 output,1,NULL,NULL,NULL

exec sp_trace_setevent 3,14,1,1

exec sp_trace_setfilter 3,10,0,7,N'SQL Server Profiler - 18df8acf-7e3f-4aee-a1fd-cadf93c4d587'

Those are exactly the one responsible for the trace configuration. The most important for you beside setting the events is the @Tracefile which specifies the file, the traces should go to. (The standard output from the second trace you find will specify the provider that directly writes it to internal memory where consumer can consume it)

The next important is

exec sp_trace_setstatus 3,1

which turns on the trace.

After starting the trace and catching some information, you might want to stop it again, using the sp_trace_setstatus with the appropriate trace Id and a status of 0 (which stop the trace but keeps the definition until the instance is restarted, so you can turn on the trace later on if you want to) or 2 (which stops and deletes the definition from the server)

Having the trace file specified during the create event, you can have a look in it using the function

 fn_trace_gettable  (  filename  ,  number_files  )

and filter with any relational query to track down the statement. That’s it. Easy ? Yes, if you practice a bit with the parameters you should be pretty quick a SQL Server tracing expert :-)

-Jens

Comments

  • Anonymous
    January 29, 2013
    very informative, thanks