Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Read large C2 Audit Mode trace files

By Michael Lascuola,

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.

Total article views: 357 | Views in the last 30 days: 3
 
Related Articles
FORUM

How to findout who has modified the Stored procedures recently

How to findout who has modified the Stored procedures recently

FORUM

trace

trace

FORUM

tracing the objects created/modified by particular user.

tracing the objects created/modified by particular user.

ARTICLE

Cursor-Killing: Retrieving Recently Modified Data

Cursors are considered by many to be the bane of good T-SQL. What are the best ways to avoid iterat...

FORUM

Trace

Trace

Tags
c2 audit mode    
fn_trace_gettable    
profiler    
trace files    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones