Default Trace in SQL Server

Leave a comment (0) Go to comments

SQL Server 2008 and 2005 has a default trace running. As BOL states: “Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.”

The default trace provides auditing on schema modifications such as table creation and stored procedure deletion etc in Schema Changes History report. You can get to this report at the database level by right-clicking on the database instance, choosing Reports>>>Standard Reports>>>Schema Changes History.

You can enforce whether it is on or off using Policy Management (SQL Server 2008). The server configuration facet has a parameter called @DefaultTraceEnabled which corresponds to turning on or off the default trace. This is only applicable to SQL Server 2008.

Querying the Default Trace

– Get information about the default trace (includes the trace file location) …

SELECT *   FROM ::fn_trace_getinfo(default)


QUERY OUTPUT

Result Set Description
Traceid Unique identifier for the trace
Property = 1 Configured trace options
Property = 2 Trace file name
Property = 3 Max file size for the *.trc file
Property = 4 Stop time for the trace session
Property = 5 Current trace status (1 = On and 0 = Off)
Value Current value for the traceid\property combination

–Show everything you can get from querying the default trace …

SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description FROM ::fn_trace_geteventinfo(1) t INNER JOIN sys.trace_events e ON t.eventID = e.trace_event_id INNER JOIN sys.trace_columns c ON t.columnid = c.trace_column_id

– Query the default trace file data …

SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName, e.category_id, cat.name, textdata, starttime, endtime, duration, eventclass, eventsubclass, e.name as EventName FROM ::fn_trace_gettable( ' <Trace File Location, which is returned by first query > ' ,0) INNER JOIN sys.trace_events e ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id

Key Note:

  • You can not modify trace settings (events etc) this default trace
  • You can stop and start the default trace using sp_configure advance option (default trace enabled)
  • By default, default trace is stored in the LOG directory for your SQL Server instance (i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\) at the root of where your SQL Server is installed.
EOF - Default Trace in SQL Server, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.