SQL server, SQLServerPedia Syndication

Trc files in the SQL log directory.

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.

Advertisements
Standard

One thought on “Trc files in the SQL log directory.

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s