Wednesday, January 30, 2013

How to upload/import multiple .trc files to a trace table in SQL Server

1. Bring each one of the trc files to the database 
SELECT * INTO trace_folder_file1 
FROM ::fn_trace_gettable('c:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Log\log_1.trc', 5) 
SELECT * INTO trace_folder_file2 
FROM ::fn_trace_gettable('c:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Log\log_2.trc', 5) 

2. Query the table to identify queries based on the information you need.
In this case it will bring all the queries that have something in the duration column 

SELECT * FROM ( 
SELECT * FROM [dbo].[trace_folder_file1] 
UNION ALL 
SELECT * FROM [dbo].[trace_folder_file1] ) 
AS DT 
WHERE [Duration] > 0