
Applies to SQL Server 2019, 2022 and 2025. The script runs in Windows PowerShell 5.1 or PowerShell 7.
The disk space alerts tell you a drive is nearly full today. This report tells you when it will be, months ahead, which is the difference between ordering disk and an emergency change on a Friday night. It keeps a daily record of every database file and the drive it sits on, and works out the growth from that.
What the report shows
- A forecast for every volume that holds database files: size, free space, growth per month over roughly the last 90 days, and months until full. Each is marked ORDER DISK NOW (under 3 months), PLAN DISK (under 6), WATCH (under 12) or OK.
- Database growth: data actually used (not just allocated), growth per month, and growth over the last year in GB and percent once there’s a year of history.
- A server summary for the quarterly pack: databases, data allocated and used, log allocated, disk total and free.
- File settings that make growth unpredictable: percentage growth, autogrowth off, tiny growth increments on big files, files over 90% of their maximum size, and files whose next growth won’t fit on the drive.
Why it matters
Storage is usually ordered on someone else’s timescale: a purchase order, a SAN team’s change window, a cloud budget approved quarterly. If the first sign of trouble is a 10% free alert, it’s already too late for any of those.
The forecast also answers the finance question. “We’ll need another 2 TB by June” with the growth figures behind it gets approved. “We’re running low” gets a meeting.
Collect daily. Look at the volume forecast monthly, and send the full report quarterly to the IT manager and whoever holds the storage budget.
The script
For each instance, it opens every online database to read the current size and used space of each file (sys.master_files on its own shows tempdb’s start-up size, not its current size), then adds the growth settings and the volume details from sys.dm_os_volume_stats. One row per file per run.
# Invoke-CapacityAudit.ps1
# One row per database file per run: size, space used, autogrowth and max size,
# and the size and free space of the volume it sits on. Keep a year of it and
# the capacity report can say how fast each database and volume is growing,
# and when each volume fills. Schedule it daily; report on it quarterly.
#
# Tables: run sql\01_Create_Tables.sql first.
# Report: sql\CapacityReport.sql
# ---- Settings ---------------------------------------------------------------
$ServerListPath = 'C:\DBA\Servers.txt'
$InventoryServer = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------
. "$PSScriptRoot\DbaReportHelpers.ps1"
$CapacityQuery = @"
SET NOCOUNT ON;
-- Current size and used pages per file, read inside each database.
-- (sys.master_files shows tempdb's start-up size, not its current size.)
CREATE TABLE #Files
(
DatabaseId INT NOT NULL,
FileId INT NOT NULL,
SizePages BIGINT NULL,
UsedPages BIGINT 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';
INSERT INTO #Files (DatabaseId, FileId, SizePages, UsedPages)
SELECT DB_ID(), file_id, size, FILEPROPERTY(name, ''SpaceUsed'')
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
d.name AS DatabaseName,
mf.name AS LogicalName,
mf.type_desc AS FileType,
mf.physical_name AS PhysicalName,
vs.volume_mount_point AS VolumeMountPoint,
CAST(COALESCE(f.SizePages, mf.size) * 8 / 1024.0 AS DECIMAL(18,2)) AS SizeMB,
CAST(f.UsedPages * 8 / 1024.0 AS DECIMAL(18,2)) AS UsedMB,
CASE WHEN mf.is_percent_growth = 0
THEN CAST(mf.growth * 8 / 1024.0 AS DECIMAL(18,2)) END AS GrowthMB,
CASE WHEN mf.is_percent_growth = 1 THEN mf.growth END AS GrowthPct,
CASE WHEN mf.max_size IN (-1, 268435456) THEN NULL -- unlimited (268435456 = 2 TB log)
ELSE CAST(mf.max_size * 8 / 1024.0 AS DECIMAL(18,2)) END AS MaxSizeMB,
CAST(vs.total_bytes / 1048576.0 AS DECIMAL(18,2)) AS VolumeTotalMB,
CAST(vs.available_bytes / 1048576.0 AS DECIMAL(18,2)) AS VolumeFreeMB
FROM sys.master_files AS mf
JOIN sys.databases AS d ON d.database_id = mf.database_id
LEFT JOIN #Files AS f ON f.DatabaseId = mf.database_id AND f.FileId = mf.file_id
OUTER APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
WHERE d.state_desc = 'ONLINE';
"@
$Servers = Get-ServerList -Path $ServerListPath
$CaptureDate = Get-Date
$Repo = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase
try {
Assert-InventoryTable $Repo 'dbo.CollectionLog', 'dbo.DatabaseFileInventory'
foreach ($Server in $Servers) {
try {
$Source = Open-SqlConnection -Server $Server
try {
$Data = Invoke-SqlQuery $Source $CapacityQuery
}
finally {
$Source.Close()
}
$Files = Add-DataTableRows $Repo 'dbo.DatabaseFileInventory' $Data $Server $CaptureDate
Write-CollectionLog $Repo -Collection 'Capacity' -ServerName $Server -CaptureDate $CaptureDate `
-Status 'SUCCESS' -Rows $Files
Write-Host "$Server : $Files database files"
}
catch {
Write-CollectionLog $Repo -Collection 'Capacity' -ServerName $Server -CaptureDate $CaptureDate `
-Status 'FAILED' -ErrorMessage $_.Exception.Message
Write-Warning "$Server : $($_.Exception.Message)"
}
}
}
finally {
$Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script). This version adds the functions the newer collectors use; it replaces the older copy and the earlier scripts still work with it.
# 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()
}
function Invoke-SqlQuerySet {
# Like Invoke-SqlQuery, but returns a DataSet: one DataTable per result set.
param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 600
foreach ($Name in $Parameters.Keys) {
$null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
}
$Set = New-Object System.Data.DataSet
$null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Set)
$Set
}
function Assert-InventoryTable {
# Stops the run early with a clear message if 01_Create_Tables.sql hasn't been run.
param($Connection, [Parameter(Mandatory)][string[]]$Table)
foreach ($Name in $Table) {
$Id = (Invoke-SqlQuery $Connection "SELECT OBJECT_ID(@Name, 'U') AS Id" @{ Name = $Name }).Rows[0].Id
if ($Id -is [System.DBNull]) {
throw "Table $Name is missing. Run sql\01_Create_Tables.sql in the DBA database first."
}
}
}
function Add-DataTableRows {
# Inserts every row of a DataTable, adding the run's CaptureDate and ServerName. Returns the row count.
param(
$Connection,
[Parameter(Mandatory)][string]$Table,
[Parameter(Mandatory)]$Data,
[Parameter(Mandatory)][string]$ServerName,
[Parameter(Mandatory)][datetime]$CaptureDate
)
foreach ($Source in $Data.Rows) {
$Row = [ordered]@{ CaptureDate = $CaptureDate; ServerName = $ServerName }
foreach ($Column in $Data.Columns.ColumnName) {
$Row[$Column] = $Source[$Column]
}
Add-InventoryRow -Connection $Connection -Table $Table -Row $Row
}
$Data.Rows.Count
}
function Write-CollectionLog {
# One row per server per collection per run, so a server that failed is never just missing.
param(
$Connection,
[Parameter(Mandatory)][string]$Collection,
[Parameter(Mandatory)][string]$ServerName,
[Parameter(Mandatory)][datetime]$CaptureDate,
[Parameter(Mandatory)][ValidateSet('SUCCESS', 'PARTIAL', 'FAILED')][string]$Status,
[int]$Rows = 0,
[string]$ErrorMessage
)
Add-InventoryRow -Connection $Connection -Table 'dbo.CollectionLog' -Row ([ordered]@{
CaptureDate = $CaptureDate
Collection = $Collection
ServerName = $ServerName
CollectionStatus = $Status
RowsCollected = $Rows
ErrorMessage = $ErrorMessage
})
}
The tables
The script stops with a clear message if these are missing. Run this once before the first collection.
-- Run once in the DBA database on the inventory server, before the first run of
-- the script. Safe to run again: each table is only created if it's missing.
IF DB_ID(N'DBA') IS NULL
CREATE DATABASE DBA;
GO
USE DBA;
GO
-- Shared by the collectors: one row per server per run, so a server that
-- failed is never just missing from the report
IF OBJECT_ID(N'dbo.CollectionLog', N'U') IS NULL
BEGIN
CREATE TABLE dbo.CollectionLog
(
CollectionLogID BIGINT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_CollectionLog PRIMARY KEY NONCLUSTERED,
CaptureDate DATETIME2(0) NOT NULL,
Collection VARCHAR(30) NOT NULL, -- Integrity, WindowsPatch, SqlPatch, Security, Capacity
ServerName NVARCHAR(128) NOT NULL,
CollectionStatus VARCHAR(20) NOT NULL, -- SUCCESS, PARTIAL, FAILED
RowsCollected INT NULL,
ErrorMessage NVARCHAR(MAX) NULL
);
CREATE CLUSTERED INDEX CX_CollectionLog
ON dbo.CollectionLog (Collection, ServerName, CaptureDate);
END;
GO
IF OBJECT_ID(N'dbo.DatabaseFileInventory', N'U') IS NULL
BEGIN
CREATE TABLE dbo.DatabaseFileInventory
(
CaptureDate DATETIME2(0) NOT NULL,
ServerName NVARCHAR(128) NOT NULL,
DatabaseName NVARCHAR(128) NOT NULL,
LogicalName NVARCHAR(128) NOT NULL,
FileType NVARCHAR(60) NULL, -- ROWS, LOG, FILESTREAM
PhysicalName NVARCHAR(260) NULL,
VolumeMountPoint NVARCHAR(260) NULL,
SizeMB DECIMAL(18,2) NULL,
UsedMB DECIMAL(18,2) NULL, -- NULL when the database couldn't be opened
GrowthMB DECIMAL(18,2) NULL, -- fixed growth; 0 = growth off
GrowthPct INT NULL, -- percentage growth
MaxSizeMB DECIMAL(18,2) NULL, -- NULL = unlimited
VolumeTotalMB DECIMAL(18,2) NULL,
VolumeFreeMB DECIMAL(18,2) NULL
);
CREATE CLUSTERED INDEX CX_DatabaseFileInventory
ON dbo.DatabaseFileInventory (ServerName, DatabaseName, CaptureDate);
END;
GO
The report queries
Run these in the DBA database. The forecast needs at least two weeks of daily runs before it shows anything and is only reliable after a couple of months; the yearly figures need a year.
/*
CapacityReport.sql - run in the DBA database.
Quarterly for the IT manager and finance; the volume forecast is also worth a
monthly look. Needs at least a few weeks of daily runs before the growth
figures mean anything, and a year for the annual ones.
*/
USE DBA;
/* 1. Volume forecast: how long until each SQL Server volume is full -------- */
WITH Vol AS
(
SELECT DISTINCT CaptureDate, ServerName, VolumeMountPoint, VolumeTotalMB, VolumeFreeMB
FROM dbo.DatabaseFileInventory
WHERE VolumeMountPoint IS NOT NULL
),
Newest AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY ServerName, VolumeMountPoint ORDER BY CaptureDate DESC) AS RN
FROM Vol
),
Baseline AS
(
-- the reading closest to 90 days ago (at least 14 days old)
SELECT *, ROW_NUMBER() OVER (PARTITION BY ServerName, VolumeMountPoint
ORDER BY ABS(DATEDIFF(DAY, CaptureDate, DATEADD(DAY, -90, SYSDATETIME())))) AS RN
FROM Vol
WHERE CaptureDate <= DATEADD(DAY, -14, SYSDATETIME())
)
SELECT
N.ServerName,
N.VolumeMountPoint,
CAST(N.VolumeTotalMB / 1024 AS DECIMAL(18,1)) AS TotalGB,
CAST(N.VolumeFreeMB / 1024 AS DECIMAL(18,1)) AS FreeGB,
CAST(100.0 * N.VolumeFreeMB / NULLIF(N.VolumeTotalMB, 0) AS DECIMAL(5,1)) AS FreePct,
G.UsedGBPerMonth,
M.MonthsUntilFull,
DATEDIFF(DAY, B.CaptureDate, N.CaptureDate) AS DaysOfHistory,
CASE
WHEN B.CaptureDate IS NULL THEN 'NOT ENOUGH HISTORY'
WHEN M.MonthsUntilFull < 3 THEN 'ORDER DISK NOW'
WHEN M.MonthsUntilFull < 6 THEN 'PLAN DISK'
WHEN M.MonthsUntilFull < 12 THEN 'WATCH'
ELSE 'OK'
END AS CapacityStatus
FROM Newest AS N
LEFT JOIN Baseline AS B
ON B.ServerName = N.ServerName AND B.VolumeMountPoint = N.VolumeMountPoint AND B.RN = 1
CROSS APPLY
(
SELECT UsedGBPerMonth = CAST((B.VolumeFreeMB - N.VolumeFreeMB) / 1024.0
/ NULLIF(DATEDIFF(DAY, B.CaptureDate, N.CaptureDate), 0) * 30.4
AS DECIMAL(18,2))
) AS G
CROSS APPLY
(
SELECT MonthsUntilFull = CAST(CASE WHEN G.UsedGBPerMonth > 0
THEN N.VolumeFreeMB / 1024.0 / G.UsedGBPerMonth END AS DECIMAL(9,1))
) AS M
WHERE N.RN = 1
ORDER BY CASE WHEN M.MonthsUntilFull IS NULL THEN 1 ELSE 0 END, M.MonthsUntilFull, N.ServerName;
/* 2. Database growth: data actually used, over 90 days and a year ---------- */
WITH Db AS
(
SELECT CaptureDate, ServerName, DatabaseName,
SUM(CASE WHEN FileType = 'ROWS' THEN UsedMB END) AS DataUsedMB,
SUM(SizeMB) AS AllocatedMB
FROM dbo.DatabaseFileInventory
GROUP BY CaptureDate, ServerName, DatabaseName
),
Ranked AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName ORDER BY CaptureDate DESC) AS Newest,
ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName
ORDER BY ABS(DATEDIFF(DAY, CaptureDate, DATEADD(DAY, -90, SYSDATETIME())))) AS Near90,
ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName
ORDER BY ABS(DATEDIFF(DAY, CaptureDate, DATEADD(DAY, -365, SYSDATETIME())))) AS Near365
FROM Db
WHERE DataUsedMB IS NOT NULL
)
SELECT
N.ServerName,
N.DatabaseName,
CAST(N.DataUsedMB / 1024 AS DECIMAL(18,2)) AS DataUsedGB,
CAST(N.AllocatedMB / 1024 AS DECIMAL(18,2)) AS AllocatedGB,
CAST((N.DataUsedMB - Q.DataUsedMB) / 1024
/ NULLIF(DATEDIFF(DAY, Q.CaptureDate, N.CaptureDate), 0) * 30.4 AS DECIMAL(18,2)) AS GrowthGBPerMonth,
CASE WHEN Y.CaptureDate <= DATEADD(DAY, -300, N.CaptureDate)
THEN CAST((N.DataUsedMB - Y.DataUsedMB) / 1024 AS DECIMAL(18,2)) END AS GrowthGBLastYear,
CASE WHEN Y.CaptureDate <= DATEADD(DAY, -300, N.CaptureDate)
THEN CAST(100.0 * (N.DataUsedMB - Y.DataUsedMB) / NULLIF(Y.DataUsedMB, 0) AS DECIMAL(9,1)) END AS GrowthPctLastYear
FROM Ranked AS N
LEFT JOIN Ranked AS Q ON Q.ServerName = N.ServerName AND Q.DatabaseName = N.DatabaseName
AND Q.Near90 = 1 AND Q.CaptureDate <= DATEADD(DAY, -14, N.CaptureDate)
LEFT JOIN Ranked AS Y ON Y.ServerName = N.ServerName AND Y.DatabaseName = N.DatabaseName AND Y.Near365 = 1
WHERE N.Newest = 1
ORDER BY GrowthGBPerMonth DESC;
/* 3. Server summary: the table for the quarterly pack --------------------- */
WITH LatestRun AS
(
SELECT ServerName, MAX(CaptureDate) AS CaptureDate
FROM dbo.CollectionLog
WHERE Collection = 'Capacity' AND CollectionStatus = 'SUCCESS'
GROUP BY ServerName
),
Files AS
(
SELECT F.*
FROM dbo.DatabaseFileInventory AS F
JOIN LatestRun AS R ON R.ServerName = F.ServerName AND R.CaptureDate = F.CaptureDate
),
Volumes AS
(
SELECT DISTINCT ServerName, VolumeMountPoint, VolumeTotalMB, VolumeFreeMB
FROM Files
WHERE VolumeMountPoint IS NOT NULL
)
SELECT
F.ServerName,
COUNT(DISTINCT F.DatabaseName) AS Databases,
CAST(SUM(CASE WHEN F.FileType = 'ROWS' THEN F.SizeMB END) / 1024 AS DECIMAL(18,1)) AS DataAllocatedGB,
CAST(SUM(CASE WHEN F.FileType = 'ROWS' THEN F.UsedMB END) / 1024 AS DECIMAL(18,1)) AS DataUsedGB,
CAST(SUM(CASE WHEN F.FileType = 'LOG' THEN F.SizeMB END) / 1024 AS DECIMAL(18,1)) AS LogAllocatedGB,
(SELECT CAST(SUM(V.VolumeTotalMB) / 1024 AS DECIMAL(18,1)) FROM Volumes AS V WHERE V.ServerName = F.ServerName) AS DiskTotalGB,
(SELECT CAST(SUM(V.VolumeFreeMB) / 1024 AS DECIMAL(18,1)) FROM Volumes AS V WHERE V.ServerName = F.ServerName) AS DiskFreeGB
FROM Files AS F
GROUP BY F.ServerName
ORDER BY F.ServerName;
/* 4. File settings that make growth unpredictable ------------------------- */
WITH LatestRun AS
(
SELECT ServerName, MAX(CaptureDate) AS CaptureDate
FROM dbo.CollectionLog
WHERE Collection = 'Capacity' AND CollectionStatus = 'SUCCESS'
GROUP BY ServerName
)
SELECT
F.ServerName, F.DatabaseName, F.LogicalName, F.FileType,
CAST(F.SizeMB / 1024 AS DECIMAL(18,2)) AS SizeGB,
F.GrowthMB, F.GrowthPct, F.MaxSizeMB,
CONCAT_WS(', ',
CASE WHEN F.GrowthPct IS NOT NULL THEN 'Percentage growth' END,
CASE WHEN F.GrowthMB = 0 THEN 'Autogrowth off' END,
CASE WHEN F.GrowthMB > 0 AND F.GrowthMB < 64 AND F.SizeMB > 1024 THEN 'Growth under 64 MB on a 1 GB+ file' END,
CASE WHEN F.MaxSizeMB IS NOT NULL AND F.UsedMB > 0.9 * F.MaxSizeMB THEN 'Over 90% of max size' END,
CASE WHEN F.GrowthMB > 0 AND F.VolumeFreeMB < F.GrowthMB THEN 'Next growth will not fit on the volume' END
) AS Problems
FROM dbo.DatabaseFileInventory AS F
JOIN LatestRun AS R ON R.ServerName = F.ServerName AND R.CaptureDate = F.CaptureDate
WHERE F.GrowthPct IS NOT NULL
OR F.GrowthMB = 0
OR (F.GrowthMB > 0 AND F.GrowthMB < 64 AND F.SizeMB > 1024)
OR (F.MaxSizeMB IS NOT NULL AND F.UsedMB > 0.9 * F.MaxSizeMB)
OR (F.GrowthMB > 0 AND F.VolumeFreeMB < F.GrowthMB)
ORDER BY F.ServerName, F.DatabaseName, F.LogicalName;
Watch out for
- The forecast is a straight line. It assumes the next three months look like the last three. Month-end, year-end, a data migration or an archive purge will all bend it, so read it alongside what you know is coming.
- It measures the drive, not just the databases. Volume growth comes from free space, so backups, exports or anything else written to the same drive counts. If a data drive is filling faster than its databases are growing, that’s worth knowing too.
- Only drives with database files.
sys.dm_os_volume_statsonly sees volumes SQL Server has files on. Backup drives need the disk space collector. - Percentage growth. 10% of a 1 TB file is 100 GB in one go. Fixed growth in MB is predictable; set it to something sensible for the file, a few hundred MB to a few GB for data.
- Log files grow slowly without instant file initialisation. Data files can skip zeroing if the service account has the Perform Volume Maintenance Tasks right. Log files are always zeroed, apart from growths up to 64 MB on SQL Server 2022 and later, so big log growths stall the database while they happen.
Where it fits
This is part of my Auditing section, alongside the disk space alerts for today’s problems and the database inventory for each database’s settings. Sizing and file settings for a new server are covered in the SQL Server DBA Handbook.
The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | Backups | SSIS errors | Integrity checks | Windows patching | SQL Server patching | Security
Clive Roberts DBA
One thought on “Capacity Report: When Each Drive Will Be Full”
Comments are closed.