Default Location on the C Drive

What this check looks for

The instance default data and log paths, from SERVERPROPERTY('InstanceDefaultDataPath') and InstanceDefaultLogPath, falling back to the registry values on builds where those properties are not available. The finding is raised when either points at the C: drive.

Why it matters

This is the cause, not the symptom. Every “database on C:” finding on the instance came from this setting.

A CREATE DATABASE statement with no ON PRIMARY ... FILENAME clause puts the files in the default location. So do:

• CREATE DATABASE [Something] typed by a developer.
• Every application installer that creates its own database.
• A restore with WITH MOVE omitted where the original path does not exist.
• Every temporary database somebody creates to test something and then forgets.
• Object Explorer in Management Studio, when a database is created through the dialog.

None of those people are making a decision about storage. They are accepting a default, which is exactly what a default is for. That is why the setting matters more than any individual misplaced database: it decides where the next one goes, and the one after that.

What the C: drive filling actually costs is the operating system, not just the database. Windows cannot extend the page file, event logs stop writing, profiles fail to load, and an administrator may be unable to log in to fix it. A full data volume is a database outage; a full system volume is a server outage, and sometimes a rebuild.

The log path matters as much as the data path and is more often overlooked. A transaction log can grow without bound when log backups stop, so a log file on C: is the fastest route to filling it. It is common to find the data path corrected and the log path still pointing at the default installation directory.

And the default backup path is the third one, which the same check family covers. A backup written to C: fills the system drive and sits on the same hardware as the database it protects.

The good news is that this is the cheapest fix in the entire scan. It takes effect immediately, needs no restart, disturbs nothing that already exists, and it prevents the problem permanently rather than correcting one instance of it.

How to confirm it yourself

What the defaults are:

SELECT SERVERPROPERTY('InstanceDefaultDataPath')   AS [default_data_path],
       SERVERPROPERTY('InstanceDefaultLogPath')    AS [default_log_path],
       SERVERPROPERTY('InstanceDefaultBackupPath') AS [default_backup_path];

On builds where those return NULL, read the registry values SQL Server actually uses:

DECLARE @dataPath NVARCHAR(512), @logPath NVARCHAR(512), @backupPath NVARCHAR(512);

EXEC master.dbo.xp_instance_regread
     N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'DefaultData', @dataPath OUTPUT;

EXEC master.dbo.xp_instance_regread
     N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'DefaultLog', @logPath OUTPUT;

EXEC master.dbo.xp_instance_regread
     N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'BackupDirectory', @backupPath OUTPUT;

SELECT @dataPath AS [default_data], @logPath AS [default_log], @backupPath AS [default_backup];

A NULL registry value is itself the finding. When DefaultData is not set, SQL Server uses the instance installation directory, which is on C: on a default installation.

What has already landed there, which tells you how long this has been true:

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],
       mf.[physical_name],
       d.[create_date]
  FROM sys.master_files AS mf WITH (NOLOCK)
 INNER JOIN sys.databases AS d WITH (NOLOCK) ON d.[database_id] = mf.[database_id]
 WHERE mf.[database_id] > 4
   AND LEFT(LOWER(mf.[physical_name]), 1) = 'c'
 ORDER BY d.[create_date] DESC;

Sort by create_date and read the most recent entries. Databases created recently on C: prove the setting is still actively producing the problem.

How much headroom the system drive has:

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]
  FROM sys.master_files AS mf WITH (NOLOCK)
 CROSS APPLY sys.dm_os_volume_stats(mf.[database_id], mf.[file_id]) AS vs;

How to fix it

Set the three paths to volumes that are not C:. It is immediate and needs no restart.

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';

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'E:\SQLBackups';

Create the folders first and give the service account full control of them. A default path the service cannot write to turns every subsequent CREATE DATABASE into a confusing failure.

Separate data and log, which is the same argument as the data-and-log-on-the-same-drive check, and this is the cheapest moment to get it right for everything created from now on.

Confirm it took:

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [data_now],
       SERVERPROPERTY('InstanceDefaultLogPath')  AS [log_now];

Those server properties are cached and may not refresh until the service restarts, so check the registry values back as well if they still look unchanged. The behavior for new databases changes immediately regardless.

Then prove it:

CREATE DATABASE [PathTest];
GO
SELECT [name], [physical_name] FROM sys.master_files WHERE [database_id] = DB_ID('PathTest');
GO
DROP DATABASE [PathTest];

Then deal with what is already on C:, which is a separate piece of work covered by the user databases on C: check. Changing the default does not move anything that exists.

And set the same defaults on every instance you manage. This is a good candidate for a standard build script, because the value of fixing it is entirely in the databases nobody has created yet.

How long it takes

About an hour, most of it deciding on the folder layout and setting the permissions. The change itself takes seconds and needs no restart.

Related reports

Related checks

Frequently asked questions

Does this change where existing databases live? No. It only affects databases created after the change. Moving existing files is separate work.

Do I need to restart SQL Server? No. New databases use the new paths immediately. The SERVERPROPERTY values may continue to report the old ones until a restart, which is a reporting artifact rather than a behavior one.

What if there is no other drive? Then the finding stands and the real item is storage provisioning. In the meantime, set MAXSIZE on database files so a runaway database fails before Windows does.

Should data and log defaults be different? Yes, where you have the volumes. It gives every new database the separation the data-and-log check asks for, without anybody having to remember.