Importing a server-side trace into a table and recycling the trace file

No Comments

Written by Luis Miguel – Originally published on November 6, 2011

Bringing the data captured by a server-side trace into a table is a very convenient way to review the trace results when monitoring and analyzing database activity. One thing I immediately thought of when implementing this kind of solution was to do it periodically and incrementally.

SQL Server 2008 does not come with built-in functionality to append data to an existing trace file or to overwrite it and start fresh using the same file name.

One possible arrangement to achieve this has been explained in other blogs and requires the use of several trace files which is SQL Server’s default behavior. Since this technique is not without flaws and I can afford to stop the data capturing process for a minute while I import the data and refresh the trace file I did come up with the following script which lets me simulate the “refreshing” feature not available in SQL Server 2008.

Run this command first to build the table that is going to store the trace data before you put the script in a store procedure or an SQL Server Agent job

INTO   db_name.schema_name.table_name
FROM   Fn_trace_gettable(‘d:\trace\my_trace_file.trc’, DEFAULT) trace 

Once you have the table in place you can use the script below to keep adding data to it as frequently as necessary or possible

DECLARE @trace_id INT
-- Use the path to your trace file to get the Id of the trace you are working with
SET @trace_id = (SELECT id
                 FROM   sys.traces
                 WHERE  [path] = ‘d:\trace\my_trace_file.trc’)
-- If you get something back the trace is running
IF @trace_id IS NOT NULL
      -- Stop the trace so it dumps the data into the file
      EXEC Sp_trace_setstatus
        @traceid = @trace_id,
        @status = 0
      -- Import the data from the trace file into a table
      INSERT INTO db_name.schema_name.table_name
      SELECT *
      FROM   Fn_trace_gettable(‘d:\trace\my_trace_file.trc’, DEFAULT) trace
      EXEC Sp_trace_setstatus
        @traceid = @trace_id,
        @status = 2
      -- Delete the trace file
      EXEC Xp_cmdshell ‘erase d:\trace\my_trace_file.trc’

Now you are ready to kick start the trace again using the same (fixed) file name you used before, probably another step in an SQL Server Agent job.

Comments are closed.

Powered by Netfirms