Categories
DBA

SQL Server 2005 Default trace

The Default Trace is implemented to audit certain events in the system. To verify if the default trace is running, run following query:

SELECT * FROM sys.configurations WHERE configuration_id = 1568

The value column 0 indicates that its disabled. It is generally enabled by default.

Default_trace1

You can turn on the advanced options and check if its enabled as shown below.

Default_trace2

To enable default trace use sp_configure options and enable as shown below. Check the config_value which is 1 implies its enabled.

Default_trace3

But should you turn it off? Before you decide, take a closer look at what is being captured. By opening the log file in the new Profiler, you can see in the event matrix exactly what is being captured. Below is a list of the events the default trace is capturing.

Database

  • Data file auto grow
  • Data file auto shrink
  • Database mirroring status change
  • Log file auto grow
  • Log file auto shrink

Errors and Warnings

  • Error log
  • Hash warning
  • Missing Column Statistics
  • Missing Join Predicate
  • Sort Warning

Full-Text

  • FT Crawl Aborted
  • FT Crawl Started
  • FT Crawl Stopped

Objects

  • Object Altered
  • Object Created
  • Object Deleted

Security Audit

  • Audit Add DB user event
  • Audit Add login to server role event
  • Audit Add Member to DB role event
  • Audit Add Role event
  • Audit Add login event
  • Audit Backup/Restore event
  • Audit Change Database owner
  • Audit DBCC event
  • Audit Database Scope GDR event (Grant, Deny, Revoke)
  • Audit Login Change Property event
  • Audit Login Failed
  • Audit Login GDR event
  • Audit Schema Object GDR event
  • Audit Schema Object Take Ownership
  • Audit Server Starts and Stops

Server

  • Server Memory Change

By default the default trace resides in the location of the SQL Server error log. In my server its located in the below mentioned path, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.

Default_trace4

To change the path of the default trace we need to change the location of the SQL Server error log. Go to SQL Server configuration manager (Go to Start Run type SqlServermanager.msc and hit enter). Change the path of the error log parameter I have changed it from

-e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ to

-e C:\Program Files\Microsoft SQL Server\ after which you will be prompted to restart SQL Services for the changes to be effective.

Default_trace5

 

Go ahead and restart the SQL Service and navigate to the new location (C:Program FilesMicrosoft SQL Server) where we will be able to see the default trace and error log.

Default_trace6

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

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

*