...and be the first to learn about new releases
The default trace is a server side trace that starts automatically when the SQL Server service is started.
It traces some of the activities and events that you may be interested in when investigating recent activity on your server.
The default trace is present in all versions of SQL Server since 2005, but is deprecated in 2012, so although it is still present there is no guarantee it will exist in future versions (extended events are taking over).
The default trace can be disabled by setting the 'default trace enabled' option to 0 with sp_configure. You may want to do this if you feel the performance overhead of the default trace is affecting overall performance but this is very unlikely unless the server is already struggling (it is very lightweight) and it's usefulness normally outweighs any performance impact.
The events in the default trace include the following:
|Database||Data File Auto Grow|
|Data File Auto Shrink|
|Database Mirroring State Change|
|Log File Auto Grow|
|Log File Auto Shrink|
|Errors and Warnings ErrorLog||Hash Warnings|
|Missing Column Statistics|
|Missing Join Predicate|
|Full text||FT:Crawl Aborted (not 2012)|
|Performance||Plan Guide Unsuccessful (2008 SP1 onwards)|
|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 Addlogin Event|
|Audit Backup/Restore Event|
|Audit Change Database Owner|
|Audit DBCc Event|
|Audit Database Scope GDR Event|
|Audit Login Change Property Event|
|Audit Login Failed|
|Audit Login GDR Event|
|Audit Schema Object GDR Event|
|Audit Schema Object Take Ownership Event|
|Audit Server Starts and Stops|
|Server||Server Memory Change|
The default trace consists of a maximum of 5 trace files. Each file is closed when it reaches 20MB in size, and events roll over into a new one. The oldest file is deleted so there are never more than 5. On a busy server, this may mean you can only go back a few minutes.
If you want to keep more than this, you could create a job to copy the older files into a different location - preferably on a different server.
To answer part of the original question, there is no way of viewing the default trace in Management Studio.
You have 3 options:
SQL Server Profiler can display the default trace in the same way as any other trace. Double click on one of the files to open it. If it is not the most recent file you will be ask if you want the next file opened.
Another way to view the default trace is via TSQL.
The function fn_trace_getable opens one or more trace files and returns the result as a table. This can be joined onto sys.trace_events to get the descriptive names for each event.
You can get the name of the current trace file by querying sys.traces as follows:
SELECT path FROM sys.traces WHERE is_default = 1
And this TSQL query filters on category 2 (Database) to see what file grow/shrink and mirroring state change events have occurred.
FROM ::fn_trace_gettable('F:\MSSQL10_50.A\MSSQL\Log\log_113.trc', 0) df
JOIN sys.trace_events e ON df.EventClass = e.trace_event_id
WHERE e.category_id = 2
ORDER BY df.StartTime DESC
And finally, there are third party tools available that make the task much easier.
Here is a screenshot from SQL CoPilot showing that I have just shrunk the AdventureWorks database.