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.




Array

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 |