Default Trace, Managed Instance, and Me

A few days ago, my manager asked me to track and report all schema changes happening on one of our Azure SQL Managed Instances.

As a SQL Server DBA, my first thought was: easy win. I knew schema changes are captured in the default trace on SQL Server, so I dusted off a script I’ve used many times before:

SELECT  
    trcdata.StartTime,  
    trcdata.EventClass,  
    evt.name AS EventName,  
    trcdata.DatabaseName,  
    trcdata.ObjectName,  
    trcdata.ObjectType,  
    trcdata.LoginName,  
    trcdata.ApplicationName,  
    trcdata.TextData  
FROM ::fn_trace_gettable(@tracefile, DEFAULT) AS trcdata  
INNER JOIN sys.trace_events AS evt  
    ON trcdata.EventClass = evt.trace_event_id  
WHERE trcdata.EventClass IN (46, 47, 164);  
        

I set this up as a SQL Agent job to run once a day and happily waited for the first report.

That’s when the surprise hit.

At 10 AM, a schema change was made. By 8 PM, when my job ran, the report showed… nothing. Zero changes. Yet, when I checked the Schema Change History in SSMS, the change was there.

Confused, I dug deeper.

• Checked the current trace file → no luck.
• Checked older trace files → found the missing event in the fourth file back.

Turns out, because we had a broker queue running, new trace files were being rolled over every 3 hours. My once-a-day job was skipping right over them.

But wait—I was sure this same setup worked fine on my on-prem servers.

So, I went back to the docs:

👉 fn_trace_gettable(@tracefile, DEFAULT)

The key line:

When the second parameter is NULL, the function reads all trace files.

That’s true on-prem. But what I realized was—on Managed Instance, DEFAULT only reads the current trace file.

That explained everything.

For now, my quick fix was simple: I changed the job schedule to run every 2 hours, ensuring I don’t miss any schema events. Not elegant, but it works.

💡 Takeaway: If you’re relying on default trace in Managed Instance, be aware it behaves differently than on-prem. DEFAULT doesn’t span multiple rollover files.

Now I’m curious: 📌 Have you hit similar surprises on Managed Instance? 📌 What creative workarounds have you used when on-prem behavior didn’t match in Azure?

I’d love to hear your stories.