Server-Side Tracing for SQL Server 2000

Server side tracing allows the collection of Profiler Trace files (.TRC) by the SQL Server itself, rather than separately running up Profiler on the server or from a PC.
There are two ways to perform server side tracing; either manually or automatically.
Please note that this information relates only to SQL Server 2000, not SQL Server 2005.
Click here for Server-Side Tracing for SQL Server 2005.

There are two methods:

• Ad hoc Manual Collection
• Automatic Collection
  

COLLECTION METHOD 1: Ad hoc Manual Collection

This method works best when it is ad hoc because the collection will stop and not restart if the database server is restarted.
Below is the entire script for this process which must be modified to suit your needs and executed in sections as described below.

Server-Side_Trace_Setup_SQL2000.zip (Size 3 KB)

STEP 1:

1. Open the script in Query Analyzer.
2. In the section below:
   • Set @tfilename with the drive and path where you want the trace files written.
   • Set @maxfilesize to the maximum size (in MB) each trace file will be before it rolls over to a new file.
   • If you are using the @stoptime parameter, then set the number of hours you want the trace to run and comment/un-comment the associated EXEC lines.
3. In the example below, the trace files will reside in E:\SQLTRACE\ and each trace file will be named by the server name and will be 100MB in size before rolling into a new file.
4. NOTE: It is important to set the variables above so that there is enough disk space to support all the trace files that will be created. Also, there is no mechanism to remove older files, so you will need to be aware how much disk space is available to you.
5. When you are happy with this section of the script, execute it on the required server on the master database.
6. Take note of the value returned in @TRACE_ID. This value is also written to the server log for your reference.

-- STEP 1: Create a trace by using sp_trace_create.
declare @TRACE_ID int
declare @tfilename nvarchar(100)
declare @maxfilesize bigint
declare @timestop datetime
set @tfilename = N'E:\SQLTRACE\'
	+ convert(nvarchar(100), SERVERPROPERTY('MachineName')) --<-- Change drive and path.
PRINT @tfilename
set @maxfilesize = 100 --<-- Change max file size (units are in MB)
set @timestop = DATEADD(hh,2,getdate()) --<-- If used, change stop time hours (currently 2 hours).
EXEC sp_trace_create @traceid = @TRACE_ID OUTPUT, 
        @options = 2,
        @tracefile = @tfilename,
        @maxfilesize = @maxfilesize, 
        @stoptime = NULL
--EXEC sp_trace_create @traceid = @TRACE_ID OUTPUT, @options = 2, @tracefile = @tfilename, 
	@maxfilesize = @maxfilesize, @stoptime = @timestop
PRINT str(@TRACE_ID)
RAISERROR ('Server Side Trace created via sp_trace_create. TraceID = %d',16, 1, @TRACE_ID) WITH LOG

STEP 2:

In the next section below, change the value of @TRACE_ID to what was displayed in Step 1 above.
Change the @eventid and @columnid to suit your data collection requirements.
This link http://msdn.microsoft.com/en-us/library/aa260314%28SQL.80%29.aspx contains more information about specific events.
When you are happy with this section of the script, execute it.

-- STEP 2: Add events with sp_trace_setevent.
BEGIN
declare @TRACE_ID int
declare @onbit bit
SET @onbit = 1
SET @TRACE_ID = 1 --<-- Change value to the same as displayed above, check server log if unsure.

For brevity sake all occurrences of sp_trace_setevent were removed. See script for full list.

END

STEP 3:

The next section below, starts the tracing.
Change the value of @TRACEID to what was displayed in Step 1 above.
You should now see a trace file created in the drive and path you previously specified in the script above.

-- STEP 3: Start the trace with sp_trace_setstatus.
-- Set @traceid to correct value.
EXEC sp_trace_setstatus @traceid = 1, @status = 1

STEP 4:

To stop the tracing, run the following.
Change the value of @TRACEID to what was displayed in Step 1 above.
You can start it again by running the script in Step 3.

-- STEP 4: Stop the trace with sp_trace_setstatus.
-- Set @traceid to correct value.
EXEC sp_trace_setstatus @traceid = 1, @status = 0

STEP 5:

If you are completely finished with the tracing, then run the following.
Make sure you run Step 4 and then run the following to remove the trace events.
Change the value of @TRACEID to what was displayed in Step 1 above.
Restarting the SQL Server will also achieve the same result.

-- STEP 5: Close the trace with sp_trace_setstatus.
-- Only run this when you're no longer interested in using the trace.
-- Set @traceid to correct value.
EXEC sp_trace_setstatus @traceid = 1, @status = 2

COLLECTION METHOD 2: Automatic Collection

This method allows for the automatic collection of trace files even after the SQL Server has been restarted.
Below is the entire script for this process which must be modified to suit your needs before being executed.
It is important to note that if the SQL Server is restarted, the trace file names will begin numbering from "1" again and not continue with the last used number before the server was restarted.

usp_Server_Side_Trace_SQL2000.zip (Size 4 KB)

Instructions:

(These instructions are also contained in usp_Server_Side_Trace_SQL2000.sql)

1. Open the script in Query Analyzer.
2. Set @tfilename with the drive and path where you want the trace files written.
3. Set @maxfilesize to the maximum size (in MB) each trace file will be before it rolls over into a new file.
4. I do not recommend using @stoptime parameter.
5. Again, it is important to set the variables above so that there is enough disk space to support all the trace files that will be created.
6. Next, change the @eventid and @columnid for each sp_tracesetevent in Step 2 of the script to suit your requirements. The ones already defined should suit most requirements.
   This link http://msdn.microsoft.com/en-us/library/aa260314%28SQL.80%29.aspx contains more information about specific events.
7. When you are happy with the script, execute it on the required server on the master database.

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

EXEC master..sp_procoption 
	@ProcName = 'usp_Server_Side_Trace_SQL2000', 
	@OptionName = 'startup', 
	@OptionValue = 'on'

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

select name from sysobjects
where objectproperty(id,'ExecIsStartup')=1

Unless you want to restart the server, you will need to kick off the tracing by running the following.

EXEC master..usp_Server_Side_Trace_SQL2000

Stop the trace by running the following, making sure that you set @traceid to correct value which can be found in the server log.

-- Stop the trace with sp_trace_setstatus.
EXEC sp_trace_setstatus @traceid = 1, @status = 0
-- Close the trace with sp_trace_setstatus.
EXEC sp_trace_setstatus @traceid = 1, @status = 2

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

EXEC master..sp_procoption 
	@ProcName = 'usp_Server_Side_Trace_SQL2000', 
	@OptionName = 'startup', 
	@OptionValue = 'off'

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

select name from sysobjects
where objectproperty(id,'ExecIsStartup')=1

Analysing the Collected Data:

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

SELECT * 
FROM fn_trace_gettable('E:\SQLTRACE\SERVERNAME_n.trc', 1)
WHERE TextData is not null

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/aa173882(SQL.80).aspx

Products | Services | Contact Us | Privacy Policy | Donate | © 2010 VirtualObjectives