
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 DATABASEand 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 STATEandVIEW ANY DEFINITIONare the finer-grained options. - Renamed servers. The backup columns only count history where
msdb.dbo.backupset.server_namematches@@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@@SERVERNAMEwithsp_dropserverandsp_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
One thought on “Database Inventory Report”
Comments are closed.