Not Enough Disk Space Leading to Restore Delays

What this check looks for

The total size of each database’s data and log files, from sys.master_files, compared against the free space on the drives those files live on, from xp_fixeddrives. Where a database could not be restored into the space available, it is reported.

xp_fixeddrives is wrapped in a TRY block, because it raises an error if any fixed drive on the server is not ready, such as an empty removable drive, and that would otherwise take the whole check down.

Why it matters

Every other backup check on this report asks whether you have a backup. This one asks whether you could use it.

A restore needs room for the restored files. Restoring in place, over the existing database, is the case people assume, and it is the one that most often is not available:

• Restoring alongside, under a different name, needs the full size again. This is what you do when the live database is damaged but still needed for reference, which is common during a corruption incident.
• Restoring to a point in time means restoring a full backup and then rolling logs forward, which needs the space for the whole database before any of it is usable.
• Even restoring over the top is not free. SQL Server needs the file space, and if the existing database was shrunk while the backup was taken at full size, the restore grows it back.

The moment you discover this is the moment you can least afford to. Restores happen during incidents. Finding out then that you need 400 GB you do not have means an emergency conversation with the storage team, or deleting something under pressure, while the outage continues.

There is a second, quieter consequence. It constrains your practice. Teams that cannot restore a copy alongside the original stop doing restore tests, because there is nowhere to put them. A backup nobody has restored is a hope rather than a plan, and a lack of space is one of the most common reasons nobody has.

How to confirm it yourself

Space needed per database against space available on its volume:

WITH db AS (
    SELECT DB_NAME(mf.[database_id]) AS [database_name],
           vs.[volume_mount_point],
           SUM(CAST(mf.[size] * 8.0 / 1024 / 1024 AS DECIMAL(12,2))) AS [needed_gb],
           MAX(CAST(vs.[available_bytes] / 1073741824.0 AS DECIMAL(12,2))) AS [free_gb]
      FROM sys.master_files AS mf WITH (NOLOCK)
     CROSS APPLY sys.dm_os_volume_stats(mf.[database_id], mf.[file_id]) AS vs
     GROUP BY DB_NAME(mf.[database_id]), vs.[volume_mount_point]
)
SELECT [database_name],
       [volume_mount_point],
       [needed_gb],
       [free_gb],
       CAST([free_gb] - [needed_gb] AS DECIMAL(12,2)) AS [headroom_gb],
       CASE WHEN [free_gb] < [needed_gb] THEN 'could not restore alongside' ELSE 'ok' END AS [verdict]
  FROM db
 ORDER BY [headroom_gb];

What a specific backup would actually need, which is the authoritative answer because it reads the backup rather than the current database:

RESTORE FILELISTONLY FROM DISK = N'D:\Backups\YourDatabase_full.bak';

The Size column is per file, in bytes, and it is the size the restore will create regardless of how large the live database is now.

How to fix it

Decide what “able to restore” means for you, then make the space match it. The three positions, in increasing order of capability:

1. Restore in place only. Needs enough free space for growth during the restore. Cheapest, and it means you cannot keep the damaged database while you work.
2. Restore alongside on the same server. Needs a second copy’s worth of space. This is what a corruption incident actually wants, and what makes restore testing possible.
3. Restore on another server. Needs the space there instead, and is the best answer because it does not compete with production at all.

Practical moves, cheapest first:

• Move the backups off the data drives. Often the fastest way to free the space, and it is its own check on this report.
• Clear out what should not be there. Old trace files, memory dumps, setup logs, detached databases nobody removed.
• Reclaim genuinely unused file space. If a database was over-sized once and will never use it, a single deliberate shrink followed by an index rebuild is defensible. Not as a routine.
• Add storage, or nominate a restore target. A second server with room, even a modest one, solves position 3 and gives you somewhere to run DBCC CHECKDB on restored copies.

Then write it down. The restore runbook should say where a restore goes and how much room it needs, so the answer exists before the incident does.

How long it takes

About half an hour to work out the shortfall. Acquiring the space is a separate change, and identifying a restore target elsewhere is often the quickest route.

Related reports

Related checks

Frequently asked questions

We restore in place, so we do not need a second copy’s worth. That works until the incident where you need the damaged database kept. It also means you cannot restore-test without a second server.

Does compression change this? It makes the backup file smaller. The restored database is full size regardless, so it does not change the space a restore needs.

We restore to a different server anyway. Then this finding is informational for that server, and the question moves to whether the target has room. Worth checking there rather than assuming.

Why does it count the log file? Because the restore creates it. A database with a 200 GB log needs that space even though almost none of it holds anything.