August 8, 2013 at 9:10 am
The reason I ask is, my employer requires setting up a trace to record certain events (they follow the DOD STIG guidelines...) and they require that the trace files be kept "online" for at least 30 days.
The problem is, the various SQL servers generate *wildly* varying numbers of records in these traces. One server might generate a couple hundred a day, not rolling over to a new file for weeks, while the Sharehog / I mean Sharepoint server generates 50k+ in less than an hour.
So, currently, every so often I have to log into a server and delete old trace files. I'd set up a scheduled Windows task to do the deletes, but they've also got Group Policies in place that prevent saving credentials, so I'd still need to manually run the job...
I could probably do an Agent Job with a CMDExec step and a proxy (which are also forbidden without a good reason,) but then I'd have to deal with the hassle of getting the OK for a proxy...
If it isn't possible (and from what I've found, it's not) to set a trace to keep X days old of files, then maybe this would be a feature MS could look into adding to SQL Server at some point...
August 8, 2013 at 10:09 am
There is no such an option in creating a trace for how many days to keep it. You can only specify size and how many files to keep.
In your sitaution I would recommend to periodically load the traces into a database table. You can setup a job that calls fn_trace_gettable function to load. But your problem is that you don't know when a particular file is filled up, and you don't want to load same file twice. In this case you can have a Load_history table where you will record trace type, file number along with timestamp, so the next time the job is running it will not load file numbers which are already in that table (that's to avoid duplicates) and not to load current file (you can find it's number in sys.traces).
August 8, 2013 at 1:24 pm
I use SQL Agent job and CmdExec as mentioned for exactly the same purpose, along with the "forfiles" command:
[font="Courier New"]
forfiles /p "f:\tracefiles" /s /m *.trc /c "cmd /c del @path" /d -60[/font]
I believe the syntax varies depending on the version of Windows. I am fortunate not to have to follow outside guidelines.
PS, the sql job will show as failed if there are no files that qualify for deletion.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply