Creating a Server Side Trace from Profiler is a pretty simple task, but can have a few problems if you forget how Profiler writes out the TSQL.

Lets start by setting up the Profiler. in the example i’ll use a normal default Template.

Open Profiler (From within SSMS you can go to Tools -> SQL Server Profiler)

 

Image

 

in Profiler connect to the server you want to monitor

 

Image(1)

 

Change the Trace name: (Note MS suggests you DO NOT USE “_” in your file Names!!!

 

Image(2)

 

Set Save to file (You can set it to anywhere you like it will be blanked out when it creates the TSQL Script)

 

Image(3)

 

Make any other changes you want to in the Events Selection. and when your Ready Hit the Run button.

This will start Profiler collecting Data. Hit Stop straight away which found at the top on the menu bar.

 

Image(4)

 

Go to File -> Export -> Script Trace Definition -> For SQL Server 2005 – SQL11…

 

Image(5)

Save the file to sensible location and open it in SSMS (You can close Profiler now!)

In SSMS the majority of the work we do is all on the SP_TRACE_CREATE proc which should be at the top of the file.

Image(6)

 

So first thing to remember is you are saving the file by default on the Server your set Profiler against. So if you pick server ServerA to trace, and change N’InsertFileNameHere’ to N’C:\Trace’ its going to ServerA\C$\Trace. NOT YOUR LOCAL DRIVE.  you can write to UNC if you want.

Second. make sure the @maxfilesize is set to what you want it to.

By default Profiler will create a Server Side Trace with only use 1 file and it will stop when that file is full.

If you are running a trace and after a short period notice that the Trace is no longer in

SELECT * FROM SYS.TRACES

you should check the error log in case you get the following error:

Image(7)

You now have 2 options. Do you want to set a max number to the files created, or set it to unlimited.

To set a maximum number of files you need to edit the @options and add the @filecount.

To make the changes we start with and change to

Profiler Created Trace:

exec @rc = sp_trace_create @TraceID output, 0 , N'C:\Trace\Blocking', @maxfilesize, @Datetime
if (@rc != 0 ) goto error

Changes needed to Set Trace to max_rollover_files

exec @rc = sp_trace_create @TraceID output, 2, N'C:\Trace\TRC201403103Blocking', @maxfilesize, @Datetime, 10
if (@rc != 0 ) goto error

Not we have changed @option = “TRACE_FILE_ROLLOVER” (@option is the second parameter) from 0 to 2

and we have added the @filecount = “max_rollover_files” value of 10 as a last parameter, meaning it will create 10 files before it starts overwriting the 1st file

Changed Needed to Create Unlimited Trace Files

exec @rc = sp_trace_create @TraceID output, 2, N'C:\Trace\TRC201403103Blocking', @maxfilesize, @Datetime
if (@rc != 0 ) goto error

Not we have changed @option = “TRACE_FILE_ROLLOVER” (@option is the second parameter) from 0 to 2

By not adding the @filecount we are just saying creating as many files as you need!

Advertisements