DBA Scripts: Create Decommission Audit Session

“I think nothing uses this database anymore” is not evidence. It is a guess, and it is the kind of guess that turns into an incident report when the thing you dropped turns out to be feeding a monthly finance job nobody remembered. Proving a database is genuinely unused takes more than checking recent connections in sys.dm_exec_sessions, because that only shows what’s connected right now, not what connects once a week, once a month, or once a quarter.

This script builds the evidence properly: a dedicated Extended Events session that captures every batch, every RPC call, and every login (successful or failed) against a specific database, or the whole instance if you leave the filter blank. Run it long enough and silence becomes proof, not assumption.

Why Decommission Audit Session Matters

Decommissioning something that’s still in use, quietly, is one of the more expensive mistakes a DBA can make, and the cost usually lands somewhere else in the business before it lands back on you:

• Batch jobs, scheduled reports, and ETL processes often connect infrequently, sometimes only at month-end or quarter-end, and a short observation window will simply miss them.
• “Nobody remembers what this is for” is a common state for legacy databases, and the people who’d know have often left.
• The cost of being wrong is asymmetric: a few extra days running this session costs almost nothing, an unplanned outage from decommissioning something that mattered costs a lot more.

When to Run This Script

• Before decommissioning or retiring a database, run this for at least 5 to 7 business days to catch weekly and end-of-period activity
• Before dropping a database you’ve inherited with no documentation
• As supporting evidence for a change request, when “we confirmed zero usage over a week” needs to be more than a claim
• Alongside a broader server decommission, using the database filter to isolate one specific database’s activity from the noise of everything else on the instance

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Create-DecommissionAuditSession
Category    : traces
Purpose     : Creates an Extended Events session that captures all T-SQL batch, RPC, successful login, and failed login activity
              against a specific database (or all databases). Use before decommissioning or retiring a database to prove zero usage.
              Run for at least 5–7 business days to catch batch jobs and end-of-period activity.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-create-decommission-audit-session/)
Requires    : ALTER ANY EVENT SESSION, VIEW SERVER STATE
*/
-- SAFE:CreatesObjects
-- IMPACT:Low
SET NOCOUNT ON;

/* ── Configuration — edit these before running ───────────────────────────── */
DECLARE @SessionName NVARCHAR(128) = N'DecommissionAudit';
DECLARE @DatabaseName NVARCHAR(128) = N''; /* target database; blank = all databases (high volume on busy servers) */
DECLARE @TraceFolder NVARCHAR(260) = N'D:\SQLTrace';
    /* Folder must exist; SQL Server service account needs write access. */
DECLARE @MaxFileMB INT = 100; /* max size per .xel file in MB */
DECLARE @MaxFiles INT = 14; /* number of rollover files; oldest is overwritten first */
DECLARE @RetentionDays INT = 7;
    /* SQL Server 2025+ (v17+) only: session auto-stops after this many days.
       On older versions this has no effect — the session runs until you stop
       it manually. Run for at least 5–7 business days to catch batch jobs
       and end-of-period processing. */
/* ─────────────────────────────────────────────────────────────────────────── */

DECLARE @ProductMajorVersion INT = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT);

/* Escape any single-quotes in the path so the dynamic SQL string stays valid */
DECLARE @FilePathRaw NVARCHAR(260) = @TraceFolder + N'\' + @SessionName + N'.xel';
DECLARE @FilePathEsc NVARCHAR(522) = REPLACE(@FilePathRaw, N'''', N'''''');

/* Database filter — applied to batch and RPC events; login events are server-level */
DECLARE @dbFilter NVARCHAR(500) = N'';
IF @DatabaseName <> N''
    SET @dbFilter = N'WHERE sqlserver.database_name = N''' + REPLACE(@DatabaseName, N'''', N'''''') + N'''';

DECLARE @sql NVARCHAR(MAX);

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = @SessionName)
BEGIN
    /* STOP only if it is actually running - ALTER ... STATE = STOP on an already
       stopped session raises Msg 25704 and prints a red error over a successful run */
    IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = @SessionName)
    BEGIN
        SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = STOP;';
        EXEC sp_executesql @sql;
    END;
    SET @sql = N'DROP EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER;';
    EXEC sp_executesql @sql;
    PRINT 'Existing session ' + @SessionName + ' stopped and dropped.';
END;

/* Build WITH clause — MAX_DURATION is SQL Server 2025+ (v17+) only */
DECLARE @WithClause NVARCHAR(500) =
    N'WITH (
    MAX_DISPATCH_LATENCY = 15 SECONDS,
    STARTUP_STATE = ON';
IF @ProductMajorVersion >= 17
    SET @WithClause = @WithClause
        + N',
    MAX_DURATION = ' + CAST(@RetentionDays AS NVARCHAR(5)) + N' DAYS';
SET @WithClause = @WithClause + N'
)';

SET @sql = N'
CREATE EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.nt_username,
        sqlserver.server_principal_name,
        sqlserver.database_name
    )
    ' + @dbFilter + N'
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.nt_username,
        sqlserver.server_principal_name,
        sqlserver.database_name
    )
    ' + @dbFilter + N'
),
ADD EVENT sqlserver.login (
    ACTION (
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.nt_username,
        sqlserver.server_principal_name,
        sqlserver.database_name
    )
    WHERE sqlserver.is_system = 0
),
ADD EVENT sqlserver.error_reported (
    /* error 18456 = Login failed; state field indicates the specific reason */
    ACTION (
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.nt_username,
        sqlserver.server_principal_name,
        sqlserver.database_name
    )
    WHERE error_number = 18456
)
ADD TARGET package0.event_file (
    SET filename = N''' + @FilePathEsc + N''',
        max_file_size = ' + CAST(@MaxFileMB AS NVARCHAR(10)) + N',
        max_rollover_files = ' + CAST(@MaxFiles  AS NVARCHAR(10)) + N'
)
' + @WithClause + N';
ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = START;';

BEGIN TRY
    EXEC sp_executesql @sql;

    SELECT
        @SessionName AS session_name,
        @FilePathRaw AS output_file,
        NULLIF(@DatabaseName, N'') AS database_filter,
        @MaxFileMB AS max_file_mb,
        @MaxFiles AS max_files,
        @MaxFileMB * @MaxFiles AS total_capacity_mb,
        CASE WHEN @ProductMajorVersion >= 17
             THEN CAST(@RetentionDays AS VARCHAR(5)) + ' days (MAX_DURATION)'
             ELSE 'manual stop (pre-2025)'
        END AS session_lifetime,
        'RUNNING' AS status,
        'IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = ' + QUOTENAME(@SessionName, '''') + ') ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + ' ON SERVER STATE = STOP; DROP EVENT SESSION ' + QUOTENAME(@SessionName) + ' ON SERVER;' AS remove_cmd;

    PRINT 'Session ' + @SessionName + ' created and started.';
    PRINT 'Output file : ' + @FilePathRaw;
    PRINT 'View data   : SSMS > Management > Extended Events > Sessions > ' + @SessionName + ' > right-click target > View Target Data';
END TRY
BEGIN CATCH
    PRINT 'ERROR creating session: ' + ERROR_MESSAGE();
    THROW;
END CATCH;

This drops and recreates the session if one already exists under the same name, then builds and starts a server-scoped Extended Events session capturing sqlserver.sql_batch_completed, sqlserver.rpc_completed, sqlserver.login, and sqlserver.error_reported (filtered to 18456), optionally filtered down to one database, writing everything to a rolling .xel file target.

Before you run this: @TraceFolder defaults to D:\SQLTrace, and that folder has to exist and be writable by the SQL Server service account before the session will start. Create the folder first, or edit @TraceFolder at the top of the script to point at a folder that already exists on your instance. Also worth deciding up front: @DatabaseName blank captures every database on the instance, which can be high volume on a busy server, set it to a specific database name to scope the audit down.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Create (or safely recreate) the decommission audit trace:
.\run.ps1 Create-DecommissionAuditSession

# To run against a remote sql server:
.\run.ps1 Create-DecommissionAuditSession -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/traces/Create-DecommissionAuditSession.sql
• powershell/wrappers/traces/Create-DecommissionAuditSession.ps1

Example Output

One confirmation row is the whole output: the session exists, it is RUNNING, and the rest of the row is the configuration it was built with, worth a glance before you walk away and leave it capturing for a week. Note database_filter reading NULL here, this run is watching every database on the instance.

Understanding the Results

Best Practices

• The trace folder must exist and be writable before you run this, same precondition as every session in this family.
• Run for the full 5 to 7 business days the script recommends, not a single day, monthly and quarterly batch jobs are exactly the activity a short window will miss.
• Scope @DatabaseName to one database when you can. Leaving it blank on a busy multi-database instance generates meaningfully more event volume and disk usage than you may need.
• Zero activity over the full observation window is evidence, not proof, pair it with a review of scheduled jobs, linked server references, and application configuration before you actually drop anything.

Microsoft’s reference covers sp_executesql, sys.dm_exec_sessions and sys.server_event_sessions in full.

Related Scripts

You may also find these scripts useful:

• Create Login Activity Session
• Create SP Execution Session
• Get Active XE Sessions
• Get XE Session Activity
• Remove XE Session
• Extended Events & Tracing (hub), the cluster this sits under
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

You can’t safely decommission what you haven’t measured. This script builds a real, queryable record of every batch, RPC call, and login against a database over as long a window as you need, so “nothing’s using this” becomes a conclusion backed by evidence rather than a guess someone made under time pressure.

Run it for a full business cycle before any decommission, then read the results back with Get XE Session Activity, that’s the companion script built to summarise exactly this session’s output.