Read large C2 Audit Mode trace files
One of the problems with extracting data from C2 Audit Mode trace files is the sheer volume of data that is usually produced on even a moderately-busy SQL Server instance.
If you need to read something recent, it's not much of an inconvenience to simply locate the .TRC file just prior to the event(s) in question, and load it (with rollover) into the SQL Server Profiler GUI. But if you have hundreds or even thousands of GB of trace files, this process is prohibitively expensive in terms of time and temp disk space.
The fn_trace_gettable system function works well for me in many cases, but one of the problems with months or years worth of C2 Audit Mode traces is that, at some point, the target disk often runs out of space, causing a break in the trace data. If you try to load trace data starting from the oldest .TRC file, chances are the function will stop loading or even return an error (like "File 'D:\TraceFiles\audittrace8675309_01.trc' either does not exist or is not a recognizable trace file") when a break in the data is encountered. Then you must find the most recent date in your saved table, and resume loading from a point near there.
The enclosed script will load each rollover trace file, starting with the most recent, saving only the columns and events you select. Because profiler trace files compress so well, I have them saved in .7z compressed format. This script will extract each ZIP file prior to finding and saving the data to a local table. It then deletes the .TRC file (but saves the original .7z compressed file), so that the whole process can be perfomed in relatively little space.
The script includes 3 files:
1) Procedure usp_InsertTraceFile
Modify this stored procedure for your environment and create in the database of your choice.
2) Unzip.cmd
A batch file for extracting archive trace files. Modify for your choice of ZIp programs, or skip if your .TRC files are not compressed.
3) ReadAuditTraces.sql
Modify and run to save trace data to a local table.
-- usp_InsertTraceFile
--
-- Thu 09/20/2012 12:48:09
-- Michael Lascuola
--
-- Stored procedure used to save profile trace data
-- from C2 Audit Profile or other large SQL Server traces
--
-- Usage:
-- EXEC usp_InsertTraceFile ZIPFilename
-- Where ZIPFilename is the compressed file containing a SQL Server .trc trace file
--
USE TraceFiles
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'usp_InsertTraceFile') AND type = 'P')
DROP PROCEDURE usp_InsertTraceFile
GO
CREATE PROCEDURE usp_InsertTraceFile
@strZIPFilename VARCHAR(255)
AS
SET NOCOUNT ON
DECLARE @strFolder VARCHAR(255) -- folder containing trace files
,@strTraceFile VARCHAR(355) -- trace file name
,@strCommand VARCHAR(355) -- dynamically built command
SET @strFolder = 'F:\TraceFiles\'
-- Replace Unzip.cmd with ZIP or gz program of your choice
SET @strCommand = 'F:\Unzip.cmd ' + @strZIPFilename
PRINT @strCommand
EXEC xp_cmdshell @strCommand, no_output;
SET @strZIPFilename = REPLACE(@strZIPFilename, '.7z', '');
SET @strTraceFile = @strFolder + @strZIPFilename
-- Create results table if it does not already exist
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'TraceResults') AND type in (N'U'))
CREATE TABLE TraceResults (
[NTUserName] [nvarchar](256) NULL,
[HostName] [nvarchar](256) NULL,
[ApplicationName] [nvarchar](256) NULL,
[LoginName] [nvarchar](256) NULL,
[StartTime] [datetime] NULL,
[EventClass] [smallint] NULL
)
INSERT INTO TraceResults
SELECT NTUserName, HostName, ApplicationName, LoginName, StartTime, EventClass
FROM fn_trace_gettable(@strTraceFile, default) Trace
INNER JOIN sys.trace_events Events
ON Trace.EventClass = Events.trace_event_id
WHERE LoginName IN ('YourDomain\AnitaFlogging', 'YourDomain\IBinStealin', 'YourDomain\PhonyPersson')
AND EventClass IN (11, 13, 14, 15, 44, 40, 42, 17, 72)
-- Delete .trc file to avoid disk space issues
SET @strCommand = 'del ' + @strTraceFile
EXEC xp_cmdshell @strCommand, no_output
GO
-- ReadAuditTraces.sql
USE TraceFiles
GO
SET NOCOUNT ON
DECLARE @strFileSpec VARCHAR(512),
@strCommand VARCHAR(512),
@strFileName VARCHAR(256)
CREATE TABLE #DirectoryListing
(RowID [int] IDENTITY(1,1) NOT NULL,
DirectoryRow VARCHAR(512),
CreateStamp datetime,
[FileBytes] BIGINT,
[FileName] VARCHAR(256))
SET @strFileSpec = 'F:\TraceFiles'
SET @strCommand = 'dir ' + @strFileSpec + ' /TC /o-d' -- Sort by date descending, format results with creation date
-- PRINT @strCommand
INSERT INTO #DirectoryListing (DirectoryRow)
EXEC master.dbo.xp_cmdshell @strCommand
-- Delete rows not containing filename
DELETE
FROM #DirectoryListing
WHERE DirectoryRow NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR DirectoryRow LIKE '%<DIR>%'
OR DirectoryRow IS NULL
-- Parse out date, size & filename
UPDATE #DirectoryListing SET CreateStamp = convert(datetime, LEFT(DirectoryRow, 20)),
FileBytes = REPLACE(SUBSTRING(DirectoryRow, 21, 19), ',', ''),
[FileName] = SUBSTRING(DirectoryRow, 40, LEN(DirectoryRow) - 39)
DECLARE curZIPFiles CURSOR FAST_FORWARD FOR
SELECT [FileName] from #DirectoryListing
ORDER BY RowID -- Preserve order
OPEN curZIPFiles
FETCH NEXT FROM curZIPFiles INTO @strFileName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print file name in case process gets interrupted
PRINT @strFileName
EXEC TraceFiles.dbo.usp_InsertTraceFile @strFileName
FETCH NEXT FROM curZIPFiles INTO @strFileName
END
CLOSE curZIPFiles
DEALLOCATE curZIPFiles
GO
:: Unzip.cmd
:: Example archive extraction batch file
f:
cd F:\TraceFiles
"c:\Program Files (x86)\7-Zip\7z.exe" e "%1"
exit 
