How to read the content of one or more trace files


To read the content of one or ore trace files we need to use the following system provided function by SQL Server:

fn_trace_gettable ( ‘filename’ , number_files )
Where

‘filename’: is the initial trace file to be read. Filename is nvarchar(256), with no default.

number_files:Indicates the number of rollover files to be read. This number includes the initial file specified in filename.The value is an integer.

Let us see how it works with the help of an example:
Step 1: let us first find the traces running in the system
SELECT * FROM sys.traces
Go
Output gave us:
Trace1Step 2: Choose the trace that you want to open. In this case we have only one trace running. Copy the value from the path column and pass it on as a parameter in the below function

SELECT * FROM sys.fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0) WHERE TextData IS NOT NULL
Output gave us:
Trace2We can save the output in a temporary table if we want look further into the data:
Use Databasename;
GO
SELECT * INTO Temp_table
FROM fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0);
GO

Advertisements

Tagged: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: