Using SQL Server Default Trace

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?

  1. 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 .

  1. 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.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |