Part of the DBA-Tools Project.
Ask most DBAs “what’s actually connecting to this server” or “is this database still in use” and you’ll get an opinion, not an answer. SQL Server doesn’t track any of this for you by default, no login history worth trusting, no record of which stored procedures actually get called, no memory of what you traced last month. Once a session ends, the evidence is gone unless you captured it yourself.
Extended Events is how you capture it yourself. It’s SQL Server’s low-overhead tracing engine, and this series of six scripts turns it into a practical, repeatable evidence-gathering toolkit: three that stand up a purpose-built trace, and three that manage and read one back.
This post is the map. It explains why these scripts exist as a set rather than six unrelated one-offs, how they fit together into a single workflow, and which one to reach for depending on the question you’re actually trying to answer.
Why Extended Events Tracing Matters
A DBA gets asked variations on the same question constantly: can I decommission this, is this service account still needed, what’s actually calling this stored procedure, is anyone still connecting from that old application server. Every one of those is really the same underlying problem, proving a negative. “Nothing’s using this” isn’t something you can confirm by looking at what’s connected right now, because that only shows this instant, not the batch job that runs once a month or the reporting query that fires every Friday at 6am.
Extended Events solves this because it’s built to run continuously and cheaply. Unlike the older SQL Trace, it’s designed for production, low overhead per session, so a trace can sit there for days or weeks capturing exactly the evidence a decommission, security review, or migration needs, without the capture itself becoming a performance problem.
The catch is that “run an XE session” isn’t one script’s worth of work. You need to create the right session for the right question, confirm it’s actually running and pointed where you expect, read the results back in a form that answers the question rather than leaving you with raw XML, and then clean it up once you’re done, because an XE session nobody remembers creating is its own small liability. That’s what these six scripts cover between them.
The Two Families
Every script in this series does one of two jobs: it either stands up a trace, or it manages one that’s already running.
Create a session, pick the one that matches your question:
- Create Login Activity Session, captures every successful and failed login, with hostname, application, and account. Use this when the question is “who’s connecting to this server.”
- Create Decommission Audit Session, captures every batch, RPC call, and login against a specific database (or the whole instance). Use this when the question is “is this database still in use.”
- Create SP Execution Session, captures stored procedure execution: which procedure, how long it took, who called it, from where. Use this when the question is “what’s actually calling this.”
Manage a session, once one’s running (or you’ve inherited a server and don’t know what’s already running):
- Get Active XE Sessions, lists every non-default session currently running, its target file, and whether it survives a restart. Run this before starting anything new, and periodically as a routine health check.
- Get Extended Events Sessions, shows every session, built-in and custom side by side with each one explicitly labeled, useful when you don’t yet know which names are the expected built-ins.
- Get XE Session Activity, reads a session’s captured
.xelfile back and rolls it up into unique callers with occurrence counts and a time range. This is how raw events become an actual answer. - Remove XE Session, lists every DBA-created session, running or stopped, and generates the exact
STOP/DROPcommand for each. It’s a generator, not an executor, nothing gets torn down until you choose to run the command it hands you.
How They Fit Together
The six scripts are designed to be used in sequence, not picked at random:
- Check what’s already running with Get Active XE Sessions, so you’re not duplicating a trace someone else stood up, or missing one that’s been running unnoticed for months.
- Stand up the session that matches your question with one of the three Create scripts. Decommissioning a database calls for Create Decommission Audit Session; a security review of who’s connecting calls for Create Login Activity Session; a migration or cleanup that hinges on procedure usage calls for Create SP Execution Session.
- Let it run for a real observation window. Days, not minutes, long enough to catch weekly and end-of-period activity. A one-hour capture will miss the month-end batch job that’s the whole reason you’re asking the question.
- Read it back with Get XE Session Activity, pointed at the session you created. This is where raw events turn into a list of who’s actually connecting, calling, or running, with how often and over what span.
- Clean up with Remove XE Session once you’ve reviewed the evidence and made your decision. A trace that outlives its purpose is just overhead and a mystery for the next DBA.
That order matters. Reading a session back before it’s had time to capture a representative window just gets you a quiet result that looks like “nothing’s using this” when the truth is “nothing’s used this in the last ten minutes.” And removing a session before you’ve actually read its output back throws away the evidence you built it to collect.
Best Practices Across the Series
- The trace folder (
D:\SQLTraceby default in all three Create scripts) has to exist and be writable by the SQL Server service account before any session will start. Create it once, up front. - Run for a full business cycle, at minimum several business days, before trusting a quiet result. Monthly and quarterly jobs are exactly the activity a short window misses.
- Scope a database filter where the script supports one. Leaving it blank captures every database on the instance, which is more volume than most questions actually need.
- Zero activity over a real observation window is evidence, not proof. Pair it with a review of scheduled jobs, linked server references, and application configuration before you act on it.
- Treat captured data, hostnames, application names, login identities, the same as any other audit evidence. Review it, use it to make the decision, then remove the session and don’t leave it lying around indefinitely.
See Also
This pillar is one of ten in DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
Extended Events tracing turns “I think nothing uses this” into “here’s what actually connected, called, or ran, over a two-week window, and here’s the evidence.” That shift, from assumption to evidence, is the entire point of this series: three scripts to stand up the right trace for the question you’re actually asking, and three to manage it responsibly once it’s running.
Start with whichever Create script matches your question, give it a real observation window, read it back, and clean up when you’re done. Used together, these six scripts are a repeatable answer to one of the most common and most consequential questions in SQL Server administration: is anyone actually still using this.
Leave a Reply