SQL Server runs a lightweight background trace called the default trace that records a wide range of server-level events: database auto-growth events, object creation and deletion, login failures, server configuration changes, and more. It’s often the first place to look when investigating an incident that didn’t make it into the error log. This article shows how to query it effectively.

Querying the Default Trace

The default trace file path is stored in sys.traces. The function sys.fn_trace_gettable reads the trace file(s) and returns rows. Joining with sys.trace_events translates the numeric EventClass codes into readable event names.

DECLARE @TraceFileName NVARCHAR(512)

-- Step 1: Find the active default trace file path
SELECT @TraceFileName = path
FROM sys.traces
WHERE id = 1

-- Step 2: Query the trace, translating EventClass codes to names
SELECT
    StartTime,
    TE.name         AS EventClass,
    TextData,
    HostName,
    ApplicationName,
    LoginName,
    SPID,           -- Session ID: use for follow-up inputbuffer queries
    ObjectName
FROM sys.fn_trace_gettable(@TraceFileName, DEFAULT) TG
LEFT JOIN sys.trace_events TE ON TG.EventClass = TE.trace_event_id
WHERE TE.name IS NOT NULL
ORDER BY StartTime DESC;

Finding the Query That Triggered an Event

If you need to find the T-SQL statement that caused a specific event — a log file autogrowth, a dropped object, a failed login — you can use the SPID from the trace to check the session’s input buffer. Important caveats: SPIDs are reused over the instance lifetime, and the input buffer is overwritten by subsequent queries.

-- Replace [SPID] with the session ID from the trace
DBCC INPUTBUFFER([SPID]);

Available Event Types

Not all events in the list below are recorded by the default trace — only a subset is enabled by default. If you need to capture events not in the default trace, you’ll need to create a custom trace or use Extended Events. Use this query to get the full list of available event IDs and names:

SELECT trace_event_id, name
FROM sys.trace_events
ORDER BY trace_event_id;

Commonly useful event types in the default trace include: auto-grow events (92, 93), object creation/deletion (46, 47), login failures (20), server configuration changes (22), and DBCC commands (116).