SQL Server Interview: What, How, Where, Which for Default Trace

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

What is SQL Server Default Trace?

The default trace is a system-defined trace which is used to record a large number of events.

How to enable/disable it?

Script to Enable and Disable the Default Trace in SQL Server

Where is trace file located?

Which type of events are captured by default trace?

Database Events:

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

Objects Events:

• Object Altered
• Object Created
• Object Deleted

Full-Text Events:

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

Errors and Warnings:

• Errorlog
• Hash warning
• Missing Column Statistics
• Missing Join Predicate
• Sort Warning

Security Audit Events:

• 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 Events:

• Server Memory Change Events