Quick Scan Report – User Databases on C Drive

What this check looks for

Files belonging to user databases whose physical_name begins with c, from sys.master_files.

Why it matters

The database is not the thing at risk. Windows is.

A database on a dedicated volume that fills causes an outage for that database: error 1105 or 9002, the application stops, and you fix it. Unpleasant and bounded.

A database on C: that fills causes something else entirely:

• Windows cannot extend the page file.
• Event logs cannot write, so the record of what happened is incomplete.
• Windows Update, profile loading and temp file creation all fail.
• Administrators may not be able to log in to fix it, because logging in needs disk space for a profile.
• The server may not restart cleanly, which turns a full disk into a rebuild.

And the database that filled it is often not the one anybody was watching. Databases end up on C: because that is the default location on a server nobody configured, so it is typically the small, forgotten databases: a vendor application, a test restore, something created by an installer.

Growth is the mechanism. A database on C: is fine until it is not. It grows, or its log grows because a log backup stopped, or somebody restores a large copy alongside it, and the system drive has a few gigabytes of headroom to absorb that.

This is the same argument as the tempdb on C: check, and it applies to any database file: the system drive is the one volume whose exhaustion costs you the operating system.

How to confirm it yourself

SELECT DB_NAME(mf.[database_id])                            AS [database_name],
       mf.[name]                                            AS [logical_name],
       mf.[type_desc],
       CAST(mf.[size] * 8.0 / 1024 AS DECIMAL(12,1))        AS [size_mb],
       CASE WHEN mf.[is_percent_growth] = 1 THEN CAST(mf.[growth] AS VARCHAR(10)) + ' %'
            ELSE CAST(mf.[growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       CASE WHEN mf.[max_size] = -1 THEN 'unlimited'
            ELSE CAST(mf.[max_size] * 8 / 1024 AS VARCHAR(20)) + ' MB' END AS [max_size],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[database_id] > 4
   AND LEFT(LOWER(mf.[physical_name]), 1) = 'c'
 ORDER BY mf.[size] DESC;

How much room C: has, which decides the urgency:

SELECT DISTINCT
       vs.[volume_mount_point],
       CAST(vs.[total_bytes]     / 1073741824.0 AS DECIMAL(12,1)) AS [total_gb],
       CAST(vs.[available_bytes] / 1073741824.0 AS DECIMAL(12,1)) AS [free_gb],
       CAST(vs.[available_bytes] * 100.0 / NULLIF(vs.[total_bytes], 0) AS DECIMAL(5,1)) AS [free_pct]
  FROM sys.master_files AS mf WITH (NOLOCK)
 CROSS APPLY sys.dm_os_volume_stats(mf.[database_id], mf.[file_id]) AS vs
 WHERE vs.[volume_mount_point] LIKE 'C%';

A file with unlimited growth on a C: drive with 10 GB free is the combination to act on first.

And the instance defaults, which determine where the next one lands:

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [default_data],
       SERVERPROPERTY('InstanceDefaultLogPath')  AS [default_log];

How to fix it

Move the files to a data volume. It needs the database offline for the copy, which is the whole cost.

-- 1. record where the files will be
ALTER DATABASE [YourDatabase]
  MODIFY FILE (NAME = N'YourDatabase',     FILENAME = N'D:\SQLData\YourDatabase.mdf');
ALTER DATABASE [YourDatabase]
  MODIFY FILE (NAME = N'YourDatabase_log', FILENAME = N'L:\SQLLogs\YourDatabase_log.ldf');

-- 2. offline
ALTER DATABASE [YourDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- 3. move the files on disk

-- 4. online
ALTER DATABASE [YourDatabase] SET ONLINE;

Check the service account has write access to the target folders before taking the database offline. If it does not, the database will not come back online and you will be fixing permissions during an outage rather than before one.

Take the log file too, and put it somewhere separate from the data if you can. That is the data and log separation check, and doing both moves at once is one outage rather than two.

Then fix the defaults, so the next database created does not land on C: as well:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLData';
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'L:\SQLLogs';

That takes effect for new databases immediately, with no restart.

If you genuinely cannot move it right now, reduce the exposure in the meantime: set a MAXSIZE on the files so the database fails before Windows does. That is a deliberate trade, choosing a bounded database outage over an unbounded server one, and it needs monitoring so the ceiling is not hit by surprise:

ALTER DATABASE [YourDatabase]
  MODIFY FILE (NAME = N'YourDatabase', MAXSIZE = 20GB);

Note that this creates the condition the file-cannot-grow check reports, which is the point: a known, monitored limit instead of a race with the operating system.

How long it takes

About four hours across an instance, most of it the file copies and the coordination for the outage windows.

Related reports

Related checks

Frequently asked questions

It is a small database that never grows. Databases said to never grow are the ones that fill C:. A MAXSIZE is the cheap way to make that statement true rather than hopeful.

C: is a 2 TB volume, so is this still a problem? Less urgent, and the failure mode is unchanged. The question is whether anything can consume the headroom, and a database with unlimited growth can.

Can I move it without downtime? Not for the existing files. You can add a new data file on another volume online, which helps new allocations, and the original file stays where it is.

What about the system databases? master, model and msdb on C: is conventional and generally fine; they are small and do not grow unpredictably. tempdb on C: is not, and has its own check.