Database Inventory Report

SQL Server Auditing: Database Inventory Report

Applies to SQL Server 2019, 2022 and 2025. The script runs in Windows PowerShell 5.1 or PowerShell 7.

The database inventory goes one level below the server inventory: every database on every instance, with its state, settings, owner, size, how full it is and when it was last backed up. It’s the report I use most often, because it’s where the small problems hide.

What the report shows

  • State and recovery model. ONLINE, OFFLINE, RESTORING, SUSPECT; FULL, BULK_LOGGED or SIMPLE.
  • Settings. Compatibility level, collation, page verify, auto close, auto shrink.
  • Owner. The login that owns the database, or NULL when that login no longer exists.
  • Size. Data file size and space used, log size and log space used.
  • Why the log can’t clear, from log_reuse_wait_desc.
  • Availability groups. Whether the database is in one, and whether this replica is the preferred one for backups.
  • Last full, differential and log backup, which feeds the backup report.
  • Growth over 30 days, in the report query.

Why it matters

Bad database settings are rarely urgent, so they never get fixed. Auto shrink fragments indexes every time it runs. Auto close makes the first query after a quiet spell wait for the database to open. Page verify set to anything other than CHECKSUM means corruption goes unnoticed until it’s too late to restore around it. A list with the flags already worked out gets these fixed in an afternoon.

The owner column matters more than it looks. Databases owned by a person’s login break when that person leaves and their account is removed, usually at the worst moment. I want them all owned by sa or a dedicated login.

And the size history is what you take to a capacity meeting. Free space on disk tells you how long the disk lasts. Space used inside the files tells you how fast the data is actually growing.

The script

This is a cleaned-up version of my original collector. It runs one query on each instance: a loop that opens each online database to read file usage and log use, then one select from sys.databases joined to the backup history. Databases it can’t open still get a row, with sizes from sys.master_files. A server it can’t reach gets a CONNECTION_FAILED row.

# Invoke-DatabaseInventory.ps1
# One row per database per server per run: state, recovery model, settings,
# owner, sizes, log use and the last full, differential and log backups.
# A server that can't be reached still gets a CONNECTION_FAILED row, so a
# silent gap never looks like a healthy server. Schedule it daily.

# ---- Settings ---------------------------------------------------------------
$ServerListPath    = 'C:\DBA\Servers.txt'
$InventoryServer   = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------

. "$PSScriptRoot\DbaReportHelpers.ps1"

# Runs on each source server
$InventoryQuery = @"
SET NOCOUNT ON;

CREATE TABLE #Sizes
(
    DatabaseName SYSNAME       NOT NULL,
    DataSizeMB   DECIMAL(18,2) NULL,
    DataUsedMB   DECIMAL(18,2) NULL,
    LogSizeMB    DECIMAL(18,2) NULL,
    LogUsedMB    DECIMAL(18,2) NULL
);

DECLARE @Name SYSNAME, @Sql NVARCHAR(MAX);

DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE state_desc = 'ONLINE' AND HAS_DBACCESS(name) = 1;

OPEN dbs;
FETCH NEXT FROM dbs INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'USE ' + QUOTENAME(@Name) + N';
DECLARE @LogUsedMB DECIMAL(18,2) =
    (SELECT used_log_space_in_bytes / 1048576.0 FROM sys.dm_db_log_space_usage);

INSERT INTO #Sizes (DatabaseName, DataSizeMB, DataUsedMB, LogSizeMB, LogUsedMB)
SELECT DB_NAME(),
       SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) * 8 / 1024.0,
       SUM(CASE WHEN type_desc = ''ROWS'' THEN FILEPROPERTY(name, ''SpaceUsed'') END) * 8 / 1024.0,
       SUM(CASE WHEN type_desc = ''LOG''  THEN size END) * 8 / 1024.0,
       @LogUsedMB
FROM sys.database_files;';

    BEGIN TRY
        EXEC sys.sp_executesql @Sql;
    END TRY
    BEGIN CATCH
        PRINT 'Skipped ' + @Name + ': ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM dbs INTO @Name;
END;

CLOSE dbs;
DEALLOCATE dbs;

SELECT
    @@SERVERNAME                                   AS InstanceName,
    d.name                                         AS DatabaseName,
    d.state_desc                                   AS StateDesc,
    d.recovery_model_desc                          AS RecoveryModel,
    d.compatibility_level                          AS CompatibilityLevel,
    d.collation_name                               AS Collation,
    SUSER_SNAME(d.owner_sid)                       AS OwnerName,
    d.page_verify_option_desc                      AS PageVerify,
    d.is_auto_close_on                             AS IsAutoClose,
    d.is_auto_shrink_on                            AS IsAutoShrink,
    d.log_reuse_wait_desc                          AS LogReuseWait,
    d.create_date                                  AS CreateDate,
    CAST(CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS BIT) AS IsInAG,
    CAST(sys.fn_hadr_backup_is_preferred_replica(d.name) AS BIT)  AS IsPreferredBackupReplica,
    COALESCE(s.DataSizeMB, f.DataSizeMB)           AS DataSizeMB,
    s.DataUsedMB,
    COALESCE(s.LogSizeMB, f.LogSizeMB)             AS LogSizeMB,
    s.LogUsedMB,
    b.LastFullBackup,
    b.LastDiffBackup,
    b.LastLogBackup
FROM sys.databases AS d
LEFT JOIN #Sizes AS s
       ON s.DatabaseName = d.name
OUTER APPLY
(
    -- Sizes for databases we couldn't open (offline, restoring, no access)
    SELECT CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8 / 1024.0 AS DECIMAL(18,2)) AS DataSizeMB,
           CAST(SUM(CASE WHEN type_desc = 'LOG'  THEN size END) * 8 / 1024.0 AS DECIMAL(18,2)) AS LogSizeMB
    FROM sys.master_files
    WHERE database_id = d.database_id
) AS f
OUTER APPLY
(
    SELECT MAX(CASE WHEN type = 'D' THEN backup_finish_date END) AS LastFullBackup,
           MAX(CASE WHEN type = 'I' THEN backup_finish_date END) AS LastDiffBackup,
           MAX(CASE WHEN type = 'L' THEN backup_finish_date END) AS LastLogBackup
    FROM msdb.dbo.backupset
    WHERE database_name = d.name
      AND server_name   = @@SERVERNAME      -- ignore history restored from other servers
) AS b
WHERE d.database_id <> 2                    -- tempdb is never backed up
ORDER BY d.name;
"@

$Servers = Get-ServerList -Path $ServerListPath
$Repo    = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase

try {
    Invoke-SqlNonQuery $Repo @"
IF OBJECT_ID('dbo.DatabaseInventory', 'U') IS NULL
    CREATE TABLE dbo.DatabaseInventory
    (
        CaptureDate              DATETIME2(0)  NOT NULL
                                 CONSTRAINT DF_DatabaseInventory_CaptureDate DEFAULT SYSDATETIME(),
        ServerName               NVARCHAR(128) NOT NULL,
        InstanceName             NVARCHAR(128) NULL,
        DatabaseName             NVARCHAR(128) NOT NULL,
        StateDesc                NVARCHAR(60)  NULL,
        RecoveryModel            NVARCHAR(60)  NULL,
        CompatibilityLevel       INT           NULL,
        Collation                NVARCHAR(128) NULL,
        OwnerName                NVARCHAR(128) NULL,
        PageVerify               NVARCHAR(60)  NULL,
        IsAutoClose              BIT           NULL,
        IsAutoShrink             BIT           NULL,
        LogReuseWait             NVARCHAR(60)  NULL,
        CreateDate               DATETIME2(0)  NULL,
        IsInAG                   BIT           NULL,
        IsPreferredBackupReplica BIT           NULL,
        DataSizeMB               DECIMAL(18,2) NULL,
        DataUsedMB               DECIMAL(18,2) NULL,
        LogSizeMB                DECIMAL(18,2) NULL,
        LogUsedMB                DECIMAL(18,2) NULL,
        LastFullBackup           DATETIME2(0)  NULL,
        LastDiffBackup           DATETIME2(0)  NULL,
        LastLogBackup            DATETIME2(0)  NULL,
        CollectionStatus         VARCHAR(20)   NOT NULL,
        ErrorMessage             NVARCHAR(MAX) NULL
    );
"@

    foreach ($Server in $Servers) {
        try {
            $Source = Open-SqlConnection -Server $Server
            try {
                $Table = Invoke-SqlQuery $Source $InventoryQuery
            }
            finally {
                $Source.Close()
            }

            if ($Table.Rows.Count -eq 0) {
                throw 'No rows returned from source server.'
            }

            foreach ($Db in $Table.Rows) {
                $Row = [ordered]@{ ServerName = $Server; CollectionStatus = 'SUCCESS' }
                foreach ($Column in $Table.Columns.ColumnName) {
                    $Row[$Column] = $Db[$Column]
                }
                Add-InventoryRow -Connection $Repo -Table 'dbo.DatabaseInventory' -Row $Row
            }

            Write-Host "$Server : $($Table.Rows.Count) databases"
        }
        catch {
            Write-Warning "$Server : $($_.Exception.Message)"
            Add-InventoryRow -Connection $Repo -Table 'dbo.DatabaseInventory' -Row ([ordered]@{
                ServerName       = $Server
                DatabaseName     = 'CONNECTION_FAILED'
                CollectionStatus = 'FAILED'
                ErrorMessage     = $_.Exception.Message
            })
        }
    }
}
finally {
    $Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script)
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
#     . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.

function Get-ServerList {
    # One server per line. Blank lines and anything after # are ignored,
    # and duplicates (in any case) are removed.
    param([Parameter(Mandatory)][string]$Path)

    Get-Content -Path $Path |
        ForEach-Object { ($_ -split '#')[0].Trim() } |
        Where-Object { $_ } |
        Sort-Object -Unique
}

function Get-ComputerName {
    # SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
    param([Parameter(Mandatory)][string]$Server)
    ($Server -split '\\')[0]
}

function Open-SqlConnection {
    param(
        [Parameter(Mandatory)][string]$Server,
        [string]$Database = 'master'
    )

    $Conn = New-Object System.Data.SqlClient.SqlConnection
    $Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
                             "TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
    $Conn.Open()
    $Conn
}

function Invoke-SqlQuery {
    # Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    foreach ($Name in $Parameters.Keys) {
        $null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
    }
    $Table = New-Object System.Data.DataTable
    $null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
    , $Table
}

function Invoke-SqlNonQuery {
    param($Connection, [string]$Sql)

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    $null = $Cmd.ExecuteNonQuery()
}

function Add-InventoryRow {
    # Inserts one row from an ordered hashtable of column = value.
    # Every value goes in as a parameter, never pasted into the SQL text.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Table,
        [Parameter(Mandatory)][System.Collections.IDictionary]$Row
    )

    $Columns = @($Row.Keys)
    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
                       (($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'

    foreach ($Column in $Columns) {
        $Value = $Row[$Column]
        if ($null -eq $Value -or $Value -is [System.DBNull] -or
            ($Value -is [string] -and $Value.Trim() -eq '')) {
            $Value = [System.DBNull]::Value
        }
        $null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
    }
    $null = $Cmd.ExecuteNonQuery()
}

The report query

The latest row for every database, its growth over 30 days and a list of flags.

-- Latest capture of every database, with settings I want flagged, and growth over 30 days.
WITH Latest AS
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName ORDER BY CaptureDate DESC) AS RN
    FROM dbo.DatabaseInventory
    WHERE CaptureDate >= DATEADD(DAY, -2, SYSDATETIME())
),
MonthAgo AS
(
    SELECT ServerName, DatabaseName, DataUsedMB,
           ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName ORDER BY CaptureDate ASC) AS RN
    FROM dbo.DatabaseInventory
    WHERE CaptureDate >= DATEADD(DAY, -30, SYSDATETIME())
      AND CollectionStatus = 'SUCCESS'
)
SELECT
    L.ServerName,
    L.DatabaseName,
    L.StateDesc,
    L.RecoveryModel,
    L.CompatibilityLevel,
    L.OwnerName,
    L.DataSizeMB,
    L.DataUsedMB,
    L.LogSizeMB,
    L.LogUsedMB,
    CAST(L.DataUsedMB - M.DataUsedMB AS DECIMAL(18,2)) AS DataGrowth30DaysMB,
    CONCAT_WS(', ',
        CASE WHEN L.CollectionStatus = 'FAILED'          THEN 'Collection failed: ' + LEFT(L.ErrorMessage, 100) END,
        CASE WHEN L.StateDesc <> 'ONLINE'                THEN 'Not online' END,
        CASE WHEN L.IsAutoClose = 1                      THEN 'AUTO_CLOSE on' END,
        CASE WHEN L.IsAutoShrink = 1                     THEN 'AUTO_SHRINK on' END,
        CASE WHEN L.PageVerify <> 'CHECKSUM'             THEN 'PAGE_VERIFY ' + L.PageVerify END,
        CASE WHEN L.OwnerName IS NULL                    THEN 'Owner login missing' END,
        CASE WHEN L.LogSizeMB > L.DataSizeMB
              AND L.LogSizeMB > 10240                    THEN 'Log bigger than data' END,
        CASE WHEN L.LogReuseWait NOT IN ('NOTHING', 'CHECKPOINT', 'LOG_BACKUP')
                                                         THEN 'Log waiting on ' + L.LogReuseWait END
    ) AS Flags,
    L.CaptureDate
FROM Latest AS L
LEFT JOIN MonthAgo AS M
       ON M.ServerName = L.ServerName AND M.DatabaseName = L.DatabaseName AND M.RN = 1
WHERE L.RN = 1
ORDER BY L.ServerName, L.DatabaseName;

Watch out for

  • Permissions. The service account needs VIEW SERVER STATE, VIEW ANY DATABASE and access to each database (or membership of a role that has it) to read file usage. Without access, the row has sizes but no used space. On SQL Server 2022 and later, VIEW SERVER PERFORMANCE STATE and VIEW ANY DEFINITION are the finer-grained options.
  • Renamed servers. The backup columns only count history where msdb.dbo.backupset.server_name matches @@SERVERNAME, to ignore history restored from elsewhere. If the Windows server was renamed and SQL Server wasn’t told, nothing matches and every database looks unbacked-up. Fix @@SERVERNAME with sp_dropserver and sp_addserver.
  • AG secondaries. A secondary that’s not readable can’t be opened, so it shows sizes but not usage. That’s expected.
  • Thousands of databases. One instance with 3,000 small databases takes a while, because the loop opens each one. Increase the command timeout rather than skip the instance.
  • LogReuseWait of LOG_BACKUP is normal in FULL recovery between log backups. It’s only a problem if it stays there and the log keeps growing, which the backup report will also catch.

Where it fits

This is part of my Auditing section, and the backup report is built directly on it. The settings it flags are explained in the Health Check chapters of the SQL Server DBA Handbook.

The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Backups | SSIS errors

Clive Roberts DBA