Jack Corbett (6/10/2008)
Eric Stimpson (6/10/2008)
You can examine the contents of the trace after you've stopped it by using this select:Therein lies the rub, I think. A trace used for auditing would need to be running as long as the server is running or you would have to deny access to the database while you examine the log.
Since I am not really familiar with server-side traces, can you output the results to a table like you can with the Profiler GUI? I would assume you can, but then you now have the performance hit of the inserts as G mentioned in one of his posts.
I was going to reply and correct that statement, but you beat me to it. No, the trace does NOT need to be stopped to examine the active trace file with the fn_trace_gettable function. And yes, the results of the query can be inserted into a table, just like any other table valued function.
If the overhead of inserting the trace into a table is that much of a concern, you have options. You can write the trace file to a share and have a different instance grab and import it for example.
Also for those of you who are not familiar/comfortable with setting up custom traces, you can actually use Profiler to set up a trace and then export the definition as a .sql file. This is a pretty good way of starting to crack the somewhat arcane syntax of the trace events etc...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/