SQL Server – default trace FAQ

11 April,2012 by Jack Vamvas

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;
sp_configure 'default trace enabled', 1;


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 ?



FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1


                                                      FROM    sys.fn_trace_getinfo(NULL) f

                                                      WHERE   f.property = 2

                                                    )), DEFAULT) 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

 Read More on default trace and security audit

SQL Server – Audit Backup\Restore Event with SQL Default trace ...

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit - SQL Server DBA

SQL Server – Last DML operation - SQL Server DBA

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – default trace FAQ

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer