SQL Server: check database size, free space and what is filling your disk

#When to use this

An alert says a drive is 90% full, or someone asks how big a database is. These queries are read-only, so they're safe on production, and you can run them in SSMS or sqlcmd.

#How much space is left on each drive?

SELECT DISTINCT
    vs.volume_mount_point AS drive,
    vs.logical_volume_name AS label,
    CAST(vs.total_bytes / 1073741824.0 AS decimal(10,1)) AS total_gb,
    CAST(vs.available_bytes / 1073741824.0 AS decimal(10,1)) AS free_gb,
    CAST(100.0 * vs.available_bytes / vs.total_bytes AS decimal(5,1)) AS free_pct
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY free_pct;

This shows only the drives that hold database files. Backups or other files on another drive won't appear here.

#How big is each database?

SELECT
    DB_NAME(database_id) AS database_name,
    CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN CAST(size AS bigint) END) * 8 / 1024.0 / 1024 AS decimal(10,2)) AS data_gb,
    CAST(SUM(CASE WHEN type_desc = 'LOG' THEN CAST(size AS bigint) END) * 8 / 1024.0 / 1024 AS decimal(10,2)) AS log_gb
FROM sys.master_files
GROUP BY database_id
ORDER BY data_gb DESC;

size is counted in 8 KB pages, which is why the query multiplies by 8.

#How much free space is inside the files?

File size on disk isn't the same as data inside the file. Run this in the database you're checking:

USE YourDatabase;

SELECT
    name AS logical_name,
    type_desc,
    physical_name,
    CAST(size * 8 / 1024.0 AS decimal(12,1)) AS file_mb,
    CAST(FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024.0 AS decimal(12,1)) AS used_mb,
    CAST((size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024.0 AS decimal(12,1)) AS free_mb
FROM sys.database_files;

Or use the quick built-in report:

EXEC sp_spaceused;

#How full are the transaction logs?

DBCC SQLPERF(LOGSPACE);

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
ORDER BY name;

log_reuse_wait_desc explains why a log can't be reused:

If the log is already full and writes are failing, follow transaction log full 9002.

Find the oldest open transaction in the current database:

DBCC OPENTRAN;

#Which tables take up the most space?

USE YourDatabase;

SELECT TOP (20)
    s.name + '.' + t.name AS table_name,
    SUM(CASE WHEN i.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS row_count,
    CAST(SUM(ps.reserved_page_count) * 8 / 1024.0 / 1024 AS decimal(10,2)) AS total_gb,
    CAST(SUM(CASE WHEN i.index_id > 1 THEN ps.reserved_page_count ELSE 0 END) * 8 / 1024.0 / 1024 AS decimal(10,2)) AS index_gb
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
JOIN sys.tables AS t ON t.object_id = ps.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
GROUP BY s.name, t.name
ORDER BY total_gb DESC;

If index_gb is larger than the data itself, review the indexes. See missing and unused indexes.

#How are files set to grow?

SELECT
    DB_NAME(database_id) AS database_name,
    name AS logical_name,
    type_desc,
    CASE WHEN is_percent_growth = 1
         THEN CAST(growth AS varchar(10)) + '%'
         ELSE CAST(growth * 8 / 1024 AS varchar(10)) + ' MB' END AS growth_setting,
    CASE WHEN max_size = -1 THEN 'Unlimited'
         ELSE CAST(max_size * 8 / 1024 AS varchar(20)) + ' MB' END AS max_size
FROM sys.master_files
ORDER BY database_name, type_desc;

Watch for:

Use a fixed size instead. Pick a value that suits how fast the file grows:

ALTER DATABASE YourDatabase
MODIFY FILE (NAME = N'YourDatabase_log', FILEGROWTH = 512MB);

#What's filling the drive?

If the SQL files don't explain it, look at the drive itself. In PowerShell:

Get-Volume | Select-Object DriveLetter, FileSystemLabel,
  @{n='SizeGB';e={[math]::Round($_.Size/1GB,1)}},
  @{n='FreeGB';e={[math]::Round($_.SizeRemaining/1GB,1)}}

Get-ChildItem 'D:\' -Directory | ForEach-Object {
  $size = (Get-ChildItem $_.FullName -Recurse -File -ErrorAction SilentlyContinue |
           Measure-Object Length -Sum).Sum
  [pscustomobject]@{ Folder = $_.FullName; GB = [math]::Round($size/1GB, 1) }
} | Sort-Object GB -Descending | Select-Object -First 10

Common culprits are old backup files, Agent job output files, crash dumps in the SQL Server LOG folder and a grown tempdb. For tempdb, read tempdb full and contention.

#Find recent growth

Check how fast backups have grown over time, which is a reasonable proxy for data growth:

SELECT
    database_name,
    CONVERT(char(7), backup_start_date, 120) AS month,
    CAST(AVG(backup_size) / 1073741824.0 AS decimal(10,2)) AS avg_full_backup_gb
FROM msdb.dbo.backupset
WHERE type = 'D'
  AND backup_start_date > DATEADD(month, -6, GETDATE())
GROUP BY database_name, CONVERT(char(7), backup_start_date, 120)
ORDER BY database_name, month;

#What to do when space runs low

Work through these in order:

1. Move or delete files you've confirmed are safe: old backups already copied elsewhere, for example.
2. Fix a log that won't clear by dealing with log_reuse_wait_desc.
3. Add disk space. In the cloud, this is often the quickest and safest fix. See expanding an Azure VM disk or extending EBS.
4. Move a database to a bigger drive.
5. Archive or purge old data, or use compression.

Warning

Don't make shrinking your routine fix. DBCC SHRINKFILE on a data file badly fragments indexes and takes a long time, and the file often grows right back. Shrink only after a one-off large deletion, in a quiet period, and ask a senior DBA if you're not sure.

Caution

Never delete .mdf, .ndf or .ldf files to free space, and never switch recovery model to break the log chain without talking to the database owner. Both can lose data or your ability to restore to a point in time.

#Prevent it next time

• Alert when a drive reaches 80% and again at 90%, so you have time to act.
• Pre-size files to expected growth, rather than relying on autogrowth.
• Use a retention job for backups, and confirm it's really deleting files.
• Make sure log backups run on every database in the full recovery model. See backup basics.