Has someone deleted a table?
Are you trying to track auto grow events? Problem scenarios such as Database autogrow and slow database recovery
When did SQL memory usage change? Read SQL Server Performance ,memory pressure and memory usage for SQL Server memory analysis
SQL Server security changes?
The default trace has loads of information .
What type of data is available from the default trace ?
Object creation, object deletion, error events, auditing events, full text events
What sort of events does the default trace file capture?
--returns full list of events
SELECT * FROM sys.trace_events
--returns a full list of categories
SELECT * FROM sys.trace_categories
--returns a full list of subclass values
SELECT *FROM sys.trace_subclass_values
How do I check default trace is on ?
SELECT * FROM sys.configurations WHERE configuration_id = 1568
How do I enable default trace ?
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'default trace enabled', 1; GO RECONFIGURE; GO
How do I find the default trace file ?
SELECT * FROM ::fn_trace_getinfo(0)
How can I list objects deleted in the last 24 hrs from a specific database ?
SELECT * FROM ::fn_trace_gettable('E:\Program Files\Microsoft SQL Server\Data\MSSQL.1\MSSQL\LOG\log_600.trc',0) tf INNER JOIN sys.trace_events te ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS tc ON te.category_id = tc.category_id WHERE databasename = 'My_DB' AND objectname IS NOT NULL AND te.category_id = 5 AND te.trace_event_id = 47 AND tf.StartTime > getdate() -1