SQL Server Default Trace

What It Is

• The Default Trace is a lightweight, always-running server-side trace introduced in SQL Server 2005.

• SQL Server writes a rolling set of .trc files capturing administrative events:

  • Object creation/deletion

  • Login creation / permission changes

  • Auto-growth events

  • Backup/restore actions

  • Server config changes

• Every SQL Server instance (except Azure SQL) has it running unless someone disabled it.

Why It Matters

• It’s a great “accidental audit log” when your team says:
  “Who changed this?”
  “Did the database grow last night?”
  “Who added this login?”

• It remains one of the fastest ways to investigate unexpected behavior without setting up a full audit or building custom Extended Events sessions.

You Can View These Events in SSMS via Extended Events

Even though Default Trace uses the old .trc format, SQL Server exposes most of the same administrative events through the Extended Events GUI in SSMS, especially:

• Database file auto-growth (database_file_size_change)

• ErrorLog_written

• object_altered / object_created / object_deleted

• login_checker events (permissions, failures)

• Server-level configuration changes

This gives you two options:
A) Read the “legacy” default trace files, or
B) Use Extended Events (XE) to see the same categories of events in a modern UI with filtering, searching, and exporting.

How to Check Your Environment

1. Is the Default Trace running?

SELECT * 
FROM sys.configurations
WHERE name = 'default trace enabled';

 2. Where are the trace files stored?

SELECT * 
FROM fn_trace_getinfo(NULL);

3. What’s inside them?

DECLARE @tracefile nvarchar(4000);

 -- Get the current default trace file path

SELECT @tracefile = CONVERT(nvarchar(4000), value)
FROM fn_trace_getinfo(NULL)
WHERE property = 2;  -- trace file path

-- Read all rollover files starting from that trace file
-- You will likely want to specify columns here instead of SELECT *

SELECT *
FROM fn_trace_gettable(@tracefile, DEFAULT)
ORDER BY StartTime DESC;

 4. Want to see events in Extended Events instead?
In SSMS:
Management >> Extended Events >> Sessions >> Right-click ‘system_health’ >> Watch Live Data
Then expand filters for:

•  deadlock events

• Severe errors

• Waits

• Blocked processes

• Memory and CPU warnings

 (Filtering is on the extended events toolbar – View >> Toolbars)

5. Who changed my Setting?!?!?!

;WITH p AS

(
   SELECT [path] =
        -- Roll the current path back to the base "log.trc" 
        --so we read ALL rollover files

        REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'

    FROM sys.traces
    WHERE is_default = 1
)

SELECT
    t.TextData,
    t.StartTime,
    t.LoginName,
    t.HostName,
    t.SPID,
    t.DatabaseName,
    t.SessionLoginName
FROM p
    CROSS APPLY sys.fn_trace_gettable(p.[path], DEFAULT) AS t
WHERE 1=1
    AND t.TextData NOT LIKE N'WITH%p%'  -- ignore the CTE text itself
    AND -- filter here
        (t.TextData LIKE '%cost threshold for parallelism%' OR
        t.TextData LIKE '%max degree%')
ORDER BY t.StartTime DESC;

 ALL OF THE ABOVE ARE “CODE FROM THE INTERNET” – TEST FIRST. TYPOS ARE A THING

 Quick Wins

• Use default trace to catch “mystery” changes without minimal overhead.

• Use XE system_health for a longer history and richer detail.

• Build lightweight alerts (auto-growth, object changes, config changes) based on trace or XE events.

The Bottom Line

Default Trace is old, but it still answers the question, “What changed?” faster than anything else.
Extended Events gives you the modern UI and the deeper details.
Use both when you need fast answers without deploying new tooling.

SQL Tidbit:

SQL Server 2025 Standard Edition doubles the memory limit from 128 in SQL 22 to 256GB in SQL 25. This is fantastic! There is also a Standard version of the Dev edition instead of Dev always being Enterprise equivalent in the past.

Don’t let SQL Server make you miss your kid’s soccer game!
First month free for new clients

Pocket DBA®

Link Party:

AWS: Trainium3 UltraServer delivers faster AI training at lower cost

Microsoft Patches 130 Vulnerabilities, Including Critical Flaws in SPNEGO and SQL Server – this is why we don’t install RTM SQL Server immediately (CU not yet available)

Please share this with anyone still running the Profiler tool on the prod servers.