skreebydba

This morning I needed to query the default trace using fn_trace_gettable. fn_trace_gettable is a table-based function that returns the contents of a SQL trace file you pass to it. Like any table-based function, you SELECT from the function like you would a table and can include a column list and WHERE predicates. Here is an sample query:

SELECT * FROM fn_trace_gettable('C:\filepath\yourtracefile.trc', DEFAULT)

The DEFAULT parameter following the file name will read all existing rollover files, in addition to the file specified. I created stored procedure sp_QueryTraceFiles to return the columns I was interested in while dynamically building the WHERE predicates based on parameters passed in. See the comments for more information.

USE master
GO
-- =============================================
-- Created By: Frank Gill - skreebydba.com
-- Date: 2014-01-30
-- Purpose: To select rows from the default trace
-- Disclaimer: The author takes no responsibility if your production system 
-- descends into a smoking hole after running this proc.  
-- Like all code on the internet, run in test before running in prod.
-- Sample Execution:
-- EXEC dbo.sp_QueryTraceFiles
--	@starttime =		'2014-01-30 12:01:59', 
--	@servername =		'YourServer',
--	@databasename =		'YourDatabase',
--	@loginname =		'YourLogin',
--	@applicationname =	'YourApplication',
--	@textdata =			'YourText',
--	@objectname =		'YourObject'
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'sp_QueryTraceFiles' 
)
   DROP PROCEDURE dbo.sp_QueryTraceFiles
GO

CREATE PROCEDURE dbo.sp_QueryTraceFiles
	@starttime DATETIME = NULL,				-- Procedure will return trace rows with a StartTime > @startime
	@servername SYSNAME = NULL,				-- Procedure will return trace rows executed from server = @servername
	@databasename SYSNAME = NULL,			-- Procedure will return trace rows executed against database = @databasename
	@loginname SYSNAME = NULL,				-- Procedure will return trace rows executed by login = @loginname
	@applicationname VARCHAR(255) = NULL,	-- Procedure will return trace rows executed by ApplicationName LIKE @applicationname
	@textdata VARCHAR(1000) = NULL,			-- Procedure will return trace rows with TextData LIKE @textdata
	@objectname SYSNAME = NULL,				-- Procedure will return trace rows executed against object = @objectname
	@noexec BIT = 0							-- @noexec = 1 will print the dynamic SQL statement instead of executing it
AS

-- Drop temp table if it exists
IF EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '%#traceinfo%')
BEGIN
	
	DROP TABLE #traceinfo
	
END

-- Declare local variables
DECLARE @tracefile VARCHAR(1000)
DECLARE @sqlstr VARCHAR(2000)
DECLARE @wherebit BIT = 0

-- Select fn_trace_getinfo into temp table #traceinfo to get the default trace file location and name
-- traceid = 1 returns the default trace
SELECT * INTO #traceinfo FROM fn_trace_getinfo(NULL)
WHERE traceid = 1

-- Select default trace file name and location into variable for use in query
-- property = 2 is the trace file name
SELECT @tracefile = CAST(value AS VARCHAR(1000)) FROM #traceinfo
WHERE property = 2

-- Build dynamic SQL string without WHERE predicates to return information from the default trace files using fn_trace_gettable
-- The DEFAULT parm will read all existing rollover files
-- Join to sys.trace_events to return the trace event name
SELECT @sqlstr = 'SELECT trc.SPID AS [Process ID], 
trc.StartTime AS [Start Time], 
trc.ServerName AS [Server Name], 
trc.DatabaseName AS [Database Name], 
trc.LoginName AS [Login Name], 
trc.ApplicationName AS [Application Name],
trc.TextData AS [Text Data],
trc.ObjectName AS [Object Name],
evt.name AS [Event Name] 
FROM fn_trace_gettable(' + '''' + @tracefile + '''' + ', DEFAULT) trc
INNER JOIN sys.trace_events evt
ON trc.EventClass = evt.trace_event_id'

-- Append WHERE predicates if parameters are passed in
IF @starttime IS NOT NULL
BEGIN

	SET @wherebit = 1
	SET @sqlstr = @sqlstr + ' WHERE trc.StartTime > ' + '''' + CAST(@starttime AS VARCHAR(30)) + ''''
	
END

IF @servername IS NOT NULL
BEGIN

	IF @wherebit = 0
	BEGIN
		
		SET @wherebit = 1	
		SET @sqlstr = @sqlstr + ' WHERE trc.ServerName = ' + '''' + @servername + ''''
		
	END
	ELSE
	BEGIN
		
		SET @sqlstr = @sqlstr + ' AND trc.ServerName = ' + '''' + @servername + ''''
		
	END
	
END 

IF @loginname IS NOT NULL
BEGIN

	IF @wherebit = 0
	BEGIN
	
		SET @wherebit = 1
		SET @sqlstr = @sqlstr + ' WHERE trc.LoginName = ' + '''' + @loginname + ''''
		
	END
	ELSE
	BEGIN
		
		SET @sqlstr = @sqlstr + ' AND trc.LoginName = ' + '''' + @loginname + ''''
		
	END
	
END

IF @applicationname IS NOT NULL
BEGIN

	IF @wherebit = 0
	BEGIN
		
		SET @wherebit = 1
		SET @sqlstr = @sqlstr + ' WHERE trc.ApplicationName LIKE ' + '''' + '%' + @applicationname + '%' + '''' 
		
	END
	ELSE
	BEGIN
		
		SET @sqlstr = @sqlstr + ' AND trc.ApplicationName LIKE ' + '''' + '%' + @applicationname + '%' + ''''
		
	END
	
END

IF @textdata IS NOT NULL
BEGIN

	IF @wherebit = 0
	BEGIN
		
		SET @wherebit = 1
		SET @sqlstr = @sqlstr + ' WHERE trc.TextData LIKE ' + '''' + '%' + @textdata + '%' + '''' 
		
	END
	ELSE
	BEGIN
		
		SET @sqlstr = @sqlstr + ' AND trc.TextData LIKE ' + '''' + '%' + @textdata + '%' + ''''
		
	END
	
END

IF @objectname IS NOT NULL
BEGIN

	IF @wherebit = 0
	BEGIN
		
		SET @wherebit = 1
		SET @sqlstr = @sqlstr + ' WHERE trc.ObjectName = ' + '''' + @objectname + ''''
		
	END
	ELSE
	BEGIN
		
		SET @sqlstr = @sqlstr + ' AND trc.ObjectName = ' + '''' + @objectname + ''''
		
	END
	
END

IF @databasename IS NOT NULL
BEGIN

	IF @wherebit = 0
	BEGIN
		
		SET @wherebit = 1
		SET @sqlstr = @sqlstr + ' WHERE trc.DatabaseName = ' + '''' + @databasename + ''''
		
	END
	ELSE
	BEGIN
		
		SET @sqlstr = @sqlstr + ' AND trc.DatabaseName = ' + '''' + @databasename + ''''
		
	END
	
END

-- Append ORDER BY clause to the dynamic SQL string
SET @sqlstr = @sqlstr + ' ORDER BY trc.StartTime DESC'

IF @noexec = 1
BEGIN

	PRINT @sqlstr

END
ELSE
BEGIN

	EXEC(@sqlstr)
	
END
GO