SQL 2005 Trace

[StephanieLaPilu]

We are running the default trace on two of our MSSQL 2005 databases and one of them is pulling in over 300gb of event data per month.  This is causing major disk space issues and when I look at most of this information, it's not helpful at all.   As these are in our PCI environment, we have been told by our auditors that we only need to pull event data from certain tables within each database and not all of the events in the default doc from RSA.   Has anyone else ever done this?   Customized it to just pull from a specific table?   If so, how do I go about that as I'm at a complete loss.   Currently, I'm going thru the default doc to comment out most of the events being pulled in the hopes this will decrease the size of these trace files. 

[RSAAdmin]

What you want to do is to modify the script to pull only the event traces that you need.  You can go through the sqlServerAudit.sql script and remove event IDs or turn them off...for instance, you might remove this section (this is just an example):

-- *************
-- *** Event ID 11 - RPCtarting
-- *** Occurs when an RPC has started.
-- *************

exec sp_trace_setevent @traceid, 11, 1, @on
exec sp_trace_setevent @traceid, 11, 2, @on
exec sp_trace_setevent @traceid, 11, 3, @on
exec sp_trace_setevent @traceid, 11, 4, @on
exec sp_trace_setevent @traceid, 11, 6, @on
exec sp_trace_setevent @traceid, 11, 7, @on
exec sp_trace_setevent @traceid, 11, 8, @on
exec sp_trace_setevent @traceid, 11, 9, @on
exec sp_trace_setevent @traceid, 11, 10, @on
exec sp_trace_setevent @traceid, 11, 11, @on
exec sp_trace_setevent @traceid, 11, 12, @on
exec sp_trace_setevent @traceid, 11, 13, @on
exec sp_trace_setevent @traceid, 11, 14, @on
exec sp_trace_setevent @traceid, 11, 15, @on
exec sp_trace_setevent @traceid, 11, 16, @on
exec sp_trace_setevent @traceid, 11, 17, @on
exec sp_trace_setevent @traceid, 11, 18, @on
exec sp_trace_setevent @traceid, 11, 19, @on
exec sp_trace_setevent @traceid, 11, 20, @on
exec sp_trace_setevent @traceid, 11, 21, @on
exec sp_trace_setevent @traceid, 11, 22, @on
exec sp_trace_setevent @traceid, 11, 23, @on
exec sp_trace_setevent @traceid, 11, 24, @on
exec sp_trace_setevent @traceid, 11, 25, @on
exec sp_trace_setevent @traceid, 11, 26, @on
exec sp_trace_setevent @traceid, 11, 28, @on
exec sp_trace_setevent @traceid, 11, 29, @on
exec sp_trace_setevent @traceid, 11, 30, @on
exec sp_trace_setevent @traceid, 11, 31, @on
exec sp_trace_setevent @traceid, 11, 32, @on
exec sp_trace_setevent @traceid, 11, 33, @on
exec sp_trace_setevent @traceid, 11, 34, @on
exec sp_trace_setevent @traceid, 11, 35, @on
exec sp_trace_setevent @traceid, 11, 36, @on
exec sp_trace_setevent @traceid, 11, 37, @on
exec sp_trace_setevent @traceid, 11, 38, @on
exec sp_trace_setevent @traceid, 11, 39, @on
exec sp_trace_setevent @traceid, 11, 40, @on
exec sp_trace_setevent @traceid, 11, 41, @on
exec sp_trace_setevent @traceid, 11, 42, @on
exec sp_trace_setevent @traceid, 11, 43, @on
exec sp_trace_setevent @traceid, 11, 44, @on

Basically, I made a copy and then went and deleted all the event IDs that I didn't need an brought the amount of longs down considerably to the point where I could easily hold a years worth of data. 

Paul