DBA Scripts: Create Decommission Audit Session

Peter Whyte

–

Part of the DBA-Tools Project.

“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
    SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@SessionName) + N' ON SERVER STATE = STOP;
    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,
        '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:

Example Output

session_name output_file database_filter max_file_mb max_files total_capacity_mb session_lifetime status
DecommissionAudit D:\SQLTrace\DecommissionAudit.xel (none) 100 14 1400 7 days (MAX_DURATION) RUNNING

Real output captured against a local SQL Server 2025 (v17) instance, @DatabaseName left blank so database_filter comes back NULL, meaning this run captures activity against every database on the instance rather than one specific target. 14 rollover files at 100MB gives 1.4GB of capacity, sized deliberately larger than the Login Activity session, batch and RPC completion events are far higher volume than logins alone.

Understanding the Results

  • database_filter confirms scope. NULL means every database is in view, a specific name means the session only captured batch and RPC events for that database (login events are always server-level, so they’re captured regardless of this filter).
  • total_capacity_mb is 1.4GB here versus 600MB for the login-only session, that difference reflects the higher event volume of capturing full batch and RPC activity, not just connection attempts.
  • session_lifetime shows 7 days (MAX_DURATION) on this SQL Server 2025 instance, matching the script’s own guidance to run for at least 5 to 7 business days. On older versions this session runs indefinitely until stopped manually.
  • remove_cmd is the exact cleanup DDL, hold onto it (or use Remove XE Session) once you’ve reviewed the evidence with Get XE Session Activity.

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.

Related Scripts

You may also find these scripts useful:

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.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *