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)
Thank this author by sharing:
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.
A batch file for extracting archive trace files. Modify for your choice of ZIp programs, or skip if your .TRC files are not compressed.
Modify and run to save trace data to a local table.
How to findout who has modified the Stored procedures recently
tracing the objects created/modified by particular user.
Cursors are considered by many to be the bane of good T-SQL. What are the best ways to avoid iterat...