Quick Scan Report – Data and Log Files on the Same Drive

What this check looks for

Databases where the drive letter of the data file and the drive letter of the log file are the same, taken from physical_name in the catalog views.

Why it matters

Two reasons, and the recoverability one is more important than the performance one even though the performance one is what gets quoted.

The recovery reason. When a database’s data files are lost but the log is intact, you can take a tail log backup, which captures every transaction since the last log backup. Restore your last full, apply the log backups, apply the tail, and you have lost nothing. That is the difference between a recovery point of zero and a recovery point of however long ago the last log backup ran.

With both on the same drive, a drive failure takes both. There is no tail to back up. You are restoring to your last log backup and losing everything since, which on a fifteen minute schedule is up to fifteen minutes of transactions, and on a daily-full-only arrangement is up to a day.

The performance reason. Data and log have opposite I/O profiles:

A log write has to complete before the transaction can commit, so log latency is added directly to every write transaction. Interleaving those sequential writes with random data reads on the same spindles makes both worse: the log write waits behind a data read, and the transaction waits behind the log write. That shows up as WRITELOG waits, which has its own check.

On modern shared storage the performance argument is weaker than it used to be. On a SAN or an all-flash array, separate drive letters may be the same physical devices anyway, and random versus sequential matters much less. The recovery argument does not weaken at all, because it is about failure domains rather than about I/O patterns.

So the question to ask about any given instance is not “are these different letters” but “would one failure take both”.

How to confirm it yourself

SELECT DB_NAME(mf.[database_id])                       AS [database_name],
       MAX(CASE WHEN mf.[type] = 0 THEN LEFT(mf.[physical_name], 3) END) AS [data_drive],
       MAX(CASE WHEN mf.[type] = 1 THEN LEFT(mf.[physical_name], 3) END) AS [log_drive],
       CASE WHEN MAX(CASE WHEN mf.[type] = 0 THEN LEFT(mf.[physical_name], 1) END)
               = MAX(CASE WHEN mf.[type] = 1 THEN LEFT(mf.[physical_name], 1) END)
            THEN 'same drive' ELSE 'separated' END      AS [verdict]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[database_id] > 4
 GROUP BY mf.[database_id]
 ORDER BY [verdict], [database_name];

Every file with its volume, which handles mount points properly:

SELECT DB_NAME(mf.[database_id]) AS [database_name],
       mf.[name], mf.[type_desc],
       vs.[volume_mount_point],
       vs.[logical_volume_name],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 CROSS APPLY sys.dm_os_volume_stats(mf.[database_id], mf.[file_id]) AS vs
 WHERE mf.[database_id] > 4
 ORDER BY [database_name], mf.[type_desc];

And whether it is actually costing you, which decides the priority:

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms] / 1000 AS [wait_seconds]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] IN ('WRITELOG', 'PAGEIOLATCH_SH', 'PAGEIOLATCH_EX')
 ORDER BY [wait_time_ms] DESC;

How to fix it

Move the log file to a different volume. It needs the database offline briefly, which is the whole cost.

-- 1. tell SQL Server where it will be
ALTER DATABASE [YourDatabase]
  MODIFY FILE (NAME = N'YourDatabase_log', FILENAME = N'L:\SQLLogs\YourDatabase_log.ldf');

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

-- 3. move the file on disk with Explorer or a copy command

-- 4. bring it back
ALTER DATABASE [YourDatabase] SET ONLINE;

Check the service account can write to the new folder before step 2. Getting that wrong means the database will not come online, and finding out at step 4 is an unnecessarily exciting moment.

Choose the destination for failure independence, not just a different letter. Ask whoever owns the storage whether the two volumes share an array, a LUN or a datastore. If they do, you have the performance benefit and not the recovery one, and the recovery one is the point.

Set the instance defaults so new databases do not repeat it:

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 is the default location check, and fixing it here saves fixing this one repeatedly.

Prioritise by database. A production database with a fifteen minute log backup schedule gains the most. A development database rebuilt from a script gains nothing, and moving it is effort for no return.

How long it takes

About half an hour per database, most of it the file copy. Downtime is the copy time.

Related reports

Related checks

Frequently asked questions

We are on a SAN, so does this matter? The performance argument is much weaker. The recovery argument depends entirely on whether the two volumes share a failure domain, which is a question for your storage team.

Is the tail log backup really that valuable? It is the difference between losing nothing and losing everything since your last log backup. On a fifteen minute schedule that is fifteen minutes of transactions.

Can I move the file without downtime? Not for a log file. The database has to be offline while the file is moved. Adding a data file on another volume is online, but the log has to be moved.

Which should move, the data or the log? The log, usually. It is smaller, so the copy is quicker and the outage shorter.