Have you ever looked into the SQL log directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\LOG) and found a bunch of log_.trc files in them. If you were like me, you may not have bothered about them, initially.
Over time, as I administered more and more servers, I couldn’t help investigating into these trc files. Here are my findings:
After SQL server (2005 and above) is installed, a trace is automatically created. This trace logs DDL commands that are done on that instance of SQL server and is know as the default trace. The default traces rolls over through 5 files and is lightweight in terms of resource usage and hence need not be disabled. Users can also create custom traces on SQL server. The command: select * from sys.traces will return a list of all traces that are created on the SQL server. On my server the row with id 1 shows the default trace and the column ‘path’ will show the current file that the trace is being written to (…\MSSQL\LOG).
The default trace is an instance level configuration and its settings can be found by using: SELECT * FROM sys.configurations WHERE configuration_id = 1568. If the ‘value in use’ column is 1 it means that the default trace is enabled. Another location to find this setting is by running: sp_configure ‘default trace enabled’. If you do not see the values, run :sp_configure ‘show advanced options’, 1; GO RECONFIGURE;
To disable the default trace, run the following command:sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘default trace enabled’, 0; GO RECONFIGURE; GO
Now that you know what these traces are, let’s have a look inside. To see the current default trace file, use the function: select * from ::fn_trace_gettable(‘C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\LOG\log_74.trc’,1). The log_74.trc file is my current default trace file which can also be found out in sys.traces or by running SELECT * FROM ::fn_trace_getinfo(0).
The eventclass column from the above result set can be joined with sys.trace_events. trace_event_id. Some other catalogs that you can use are:sys.trace_categories sys.trace_columns sys.trace_events sys.trace_event_bindings sys.trace_subclass_values
These catalogs can be joined with the fn_trace_gettable function to find out DDL information like create/alter/deletes on objects in the SQL instance. The default trace serves good when you need to audit your databases for unauthorized object manipulation as it is created by default.
When SQL profiler is used, similar user defined traces are being created. There is some overhead to running SQL profiler though (GUI and tracing). One way to lessen the burden is by creating server side traces. I will write about this in another post.