VirtualObjectives

Server-Side Tracing

Stored procedure that allows for the easy collection of server-side trace files. With added steps you can even start server side tracing after the SQL Server has been restarted.

Download usp_Server_Side_Trace.zip (Size 5 KiB)

Instructions:

Install the stored procedure into any database. However, if you wish to automatically restart the tracing then install this stored procedure into the master database.

Once installed, this stored procedure can be used to query and set up server side traces.

Run 'EXEC usp_Server_Side_Trace' to display help using the stored procedure.

The actions are:

CREATE:
Creates a trace using the SQL Server's instance name as the file.
You must include @Path and the path must already exist on the server.
@Path : Path where trace files are written. Include trailing slash. Used with CREATE action only.
@MaxFileSize : Maximum size of trace files in MiB. Default is 100MiB.
@MaxNumFiles : Maximum number of trace files created. Default is 10.

ADDEVENT:
A trace will not start until events are added to the trace.
Add an event to the trace. You must include @EventID
Add multiple events repeatedly.
You cannot add events while the trace is running. Use STOP first.

DELEVENT:
Delete an event from the trace. You must include @EventID
You cannot delete events while the trace is running. Use STOP first.

LISTEVENTS:
This will display two lists of all the possible Events and Categories.
One is sorted by category and the other by event ID.
This will help you associate an Event ID with its function.

STATUS:
This will show current status of trace, as well as other traces on the server (including the default trace)
It will also display any events already registered against the trace.

START:
This will start the trace provided that it was created and at least one event is registered.

STOP:
This will stop the trace.

REMOVE:
Removes the trace. STOP is first required before REMOVE can be used.
It will not remove trace files from the server.


Example:

A typical script to set up and run is shown below...

This link http://msdn.microsoft.com/en-us/library/ms186265%28v=sql.105%29.aspx contains more information about specific events.


Restarting Automatically:

Server side traces are lost when the SQL Server is restarted.
To automatically start tracing then you can do the following...

1) Create a stored procedure in the master database of your server using the script similar to above.
   You will need to modify the parameters to suit your server as well as the events you want to trace.

2) Run the following to allow the stored procedure to run on server startup.

3) Run the following to check that the stored procedure is set to execute on server startup.

4) Unless you want to restart the server, you will need to kick off the tracing by running your stored procedure.

To disable the execution of stored procedure on server startup then run...

Run the following to check that the stored procedure is not set to execute on server startup.


Analysing the Collected Data:

If you know the location and file name of a trace file, you can use SSMS to read the file using a SELECT query. You can read multiple files by changing the second parameter of fn_trace_gettable.

In essence you can search the trace file for particular attributes and sort/order the data returned.
NOTE: Data in columns like Duration are in microseconds rather than milliseconds as seen from Profiler.

Information on SQL Profiler Data Columns: http://msdn.microsoft.com/en-us/library/ms190762.aspx