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.
You can turn on the advanced options and check if its enabled as shown below.
To enable default trace use sp_configure options and enable as shown below. Check the config_value which is 1 implies its enabled.
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.
- 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
- FT Crawl Aborted
- FT Crawl Started
- FT Crawl Stopped
- Object Altered
- Object Created
- Object Deleted
- 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 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.
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.
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.