Trigger Happy Rotating Header Image

May 1st, 2010:

How to run a server-side trace

This is my first “how to” post.  In stark contrast to the “for dummies” and idiot’s books, my posts will assume a little basic knowledge.  For example, when I say “run profiler” below, I assume you don’t need a screenshot showing you where in the start menu it probably is on your computer.  If you need a little more detail, that doesn’t make you a dummy or an idiot, just post a comment and I’ll do my best to help out.

I assume you already know how to run a profiler trace.  Note that when I say “profiler trace” vs. a “Server-side trace” I’m talking about a trace that is run from within profiler.  Both types run traces on the server, but a profiler trace captures the output and displays it on your screen while a server-side trace sends the output directly to an output device (usually a file on the server) with no interaction from the client while it is running.  Linchi Shea’s article illustrates why you might want to do a server-side trace – this article is not about the “why” but I will only mention that my rule of thumb is that if the server you are tracing is a “production” server or if other users are relying on their connections to it in any way (which is pretty much the definition of a production server, but it could be a UAT server or some other well-used testing server) I don’t go near it with a profiler trace (well you’ll see later that I go *near* it, but you know what I mean).

In broad strokes, we will be generating a script for our trace from profiler, running that script on our server, then looking at a couple of ways to analyze the output.

Here are the steps:

  • Run profiler and configure the trace as you would normally.  As you probably know, it’s a good idea to add some filters, limit the number of events you are tracing and limit the columns to what you are looking for – this post isn’t about “the what” either.  What to trace is dependent on what you’re looking for – I hope you know that if you’re messing with profiler in the first place.
  • Run the trace in profiler (yes, you heard me) but stop it right away.  If you feel you absolutely can’t even do this to your server, you won’t be able to generate the script from profiler, you’ll have to go through the tedious process of writing the script yourself.  We need to do this in order to get access to the menu item we’ll need in the next step.
  • Now that you have access to the File menu, go File | Export | Script Trace Definition and choose the appropriate option for your server after that.  You will be asked to save a script file somewhere
  • Open the script file in Management Studio.  You will need to make one change – You will see the string “InsertFileNameHere” (probably on line 19).  The comment above this line has useful information – read it.  I will only add that, depending on the nature of your server and your trace, your DBA might be quite particular about where files like this go.  If you’re not sure, run the following line to get the default backup directory and put it there – this should be relatively safe.

EXEC master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\mssqlserver',N'backupdirectory'

  • Execute the script – take note of the number it gives you at the end for TraceID – you will need it to shut down the trace.  At this point your trace is running – if you shut down your computer and go on holidays, it will continue running on the server until something happens – it is a server side trace, after all.
  • You can view the results of the trace at any time in profiler by just double-clicking on the TRC file you chose in the script – even while it is running (note that in Windows 7 you probably have to explicitly give yourself access to the file).  You can close the results and re-open them to get fresh entries.  This is not the same as a profiler trace because it’s just showing you what was in the file when you double-clicked it – profiler is not actively retrieving updates from your server.  This approach has most of the advantages of running profiler traces without the disadvantages.
  • When you need to stop the trace, you’ll need the following code:

EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2

  • The first line stops the trace, the second line deletes the trace definition from the server.  Note that you need to put the TraceID you made not of above in here – if you didn’t make note of it, you’ll have to figure it out by looking at the results of this:

SELECT * FROM fn_trace_getinfo(NULL)

  • Hint: it’s probably the one with the highest number
  • You can get the trace results into SQL server with the following command:

SELECT * FROM fn_trace_gettable('InsertFileNameHere',1)

That’s about it – there are many options with rotating files once they are of a certain size and stuff like that – read up on it and have fun – and don’t forget to stop your trace 🙂