A Black Box in SQL Server 2005/2008

Starting from SQL Server 2005, Microsoft added "Default Trace"

feature. It is a server side trace and it enabled by default, but

you can turn it off/on using sp_configure.

Note: Did you know the color of an airplane`s black boxis actually fluorescent orange or very bright red ,so it can
be easily spotted in plane crash.
The default trace can be very helpful in situations when
you need to know who altered/created/deleted some db object
and etc. You can also use it troubleshoot problems.

The default trace file records the last 5MB of activity on

the SQL Server IO. The max size of the trace is 20MB.
The default trace log is stored by default in the

\MSSQL\LOG directory using a rollover trace file.

In a typical installation of SQL Server, the default trace is enabled
and gets TraceID=1.
How to check if it is enabled?
SELECT *
FROM sys.configurations
WHERE [configuration_id]=1568



Where are the files located?

SELECT *
FROM fn_trace_getinfo(default)




or you can use this query

SELECT *
FROM sys.tracesWHERE is_default=1




You see the content of a trace file by executing this:
SELECT *
FROM ::fn_trace_gettable('Full Path Of the trace file',0)
Of course you can also open the files via Profiler application.

The Default Trace is also used by SQL Server in some built-in reports,
for example: Schema Changes History report.


The default trace does not capture all trace events.
The following events are captured:

Database:
Data file auto grow
Data file auto shrink
Database mirroring status change
Log file auto grow
Log file auto shrink
Errors and Warnings:
Errorlog
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

PS:
The Default Trace cannot be modified. If you want to collect
different events/data or change the location of the trace files,
you need to create your own trace.