Read your SQL trace files from SSMS using Transact-SQL

As a DBA/developer, you probably face a scenario where you need to trace your SQL Server for various reasons. This blog digs directly into how to read the trace (*.trc) file after you capture the required data. This blog would not demonstrate how to capture a trace on SQL Server. It is assumed that readers have meet the pre-requisite of using SQL Profiler or other tracing tools to collect the trace.

drop table #test_table --drop temp test table if exist

--Load trace data into a temp table with or without a filter(where clause)
select * into #test_tablefrom :: fn_trace_gettable('C:\Trace\test_trace.trc',default)  --specify the location of trace file
-- where ApplicationName = 'Microsoft SQL Server Management Studio' --uncomment to add where clause
-- and EventClass IN ( '%Error%')  --uncomment to add filter

--Query the data
SELECT TE.name,T.* FROM #test_table T -- table that contains the trace results         
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id 
where Te.name LIKE '%RPC%' ORDER BY name

The events selected during the trace will be populated to table created by the SELECT INTO command.

We now have the ability to query the trace data in almost any fashion possible using the powerful T-SQL language.