What is Default Trace?
A default trace in SQL Server is a standard server side trace which is installed by default in SQL Server 2005 onwards.
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.”
Thus SQL Server has a trace which records useful data and information of your SQL Server environment, which can become very useful when troubleshooting your environment.
How do you enable default trace?
By default , default trace is enabled but you do have the option of disabling it:
EXEC master.dbo.sp_configure ‘allow updates’, 1;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 1;
GO
— 0 to Disable 1 to Enable
EXEC master.dbo.sp_configure ‘default trace enabled’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure ‘show advanced options’, 0;
GO
EXEC master.dbo.sp_configure ‘allow updates’, 0;
GO
In SQL Server 2008, by using policies and conditions in Policy Management you can vary where the default is enabled.
The server configuration facet has a parameter called @DefaultTraceEnabled which corresponds to turning on or off the default trace. For instance, here is a Policy Management Condition which enforces the default trace being on
By using that condition you can create a policy and evaluate this policy.
What Information is Available in Default Trace?
By running following query you can get the properties of the default trace:
SELECT *
FROM fn_trace_getinfo(default);
GO
From BOL,
|
Column Name |
Data Type |
Description |
|
TraceID |
Int |
ID of the trace |
|
Property |
Int |
Property of the trace: 1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).
2 = File name
3 = Max size
4 = Stop time
5 = Current trace status |
|
Value |
Sql_variant |
Information about the property of the trace specified. |
Therefore, max size of the trace is 20MB. When the max_file_size is reached, the current trace file is closed and a new file is created. A new trace will be created every time you restart your SQL Server instance. SQL Server will automatically keep a history of 5 traces.
If you want to find out what are the available values with default trace you can run following query:
SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description
FROM ::fn_trace_geteventinfo(1) t
JOIN sys.trace_events e ON t.eventID = e.trace_event_id
JOIN sys.trace_columns c ON t.columnid = c.trace_column_id
How To Access Default Traces?
-
SQL Server Profiler
If you need to view this in an graphic interface, Profiler is the best tool. Load any trace file in Profilers and it will load the subsequent trace files. For example, if you have trace files log_70, log_71, log_72, log_73, log_74. If you load log_70 it will ask to load 71 to 74 files .
-
T-SQL
By using T-SQL, you can run you own queries including Order, Group, Count, Where
SELECT *
FROM [fn_trace_gettable](‘C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLLoglog_72.trc’, DEFAULT)
ORDER BY StartTime;
Other Information
You might have seen, there are several built-in reports:
Configuration Changes History and Schema Changes History reports are generated by reading the default traces.
]]>