Quick Scan Report – TempDB on Same Drive As Data Files

What this check looks for

TempDB data files on the same drive letter as user database data files, from sys.master_files.

Why it matters

The argument here is not primarily about performance. It is that tempdb’s size is controlled by whoever writes the next query, and sharing a volume with it means they control your data drive’s free space too.

Any user can fill tempdb. A missing join predicate producing a cartesian product, a sort over far more rows than the plan estimated, a large hash spill, an unbounded version store from a long open transaction under snapshot isolation. None of these require elevated permissions. They require one badly written query, and tempdb grows to absorb it.

If tempdb shares a volume with user data files, that query fills the data drive. The consequences are much worse than a slow query:

• Every database on that volume stops being able to grow, producing error 1105 on inserts.
• Transaction logs on the same volume cannot grow either, which can leave databases in a state needing intervention.
• SQL Server may be unable to restart, because tempdb is recreated at startup and needs the space to do it. That is the failure that turns a bad query into an extended outage: the fix for a full tempdb is often a restart, and the restart cannot complete.
• Recovery of the user databases is slowed by the same contention.

Then the performance argument, which is real but secondary. TempDB is usually the busiest database on the instance, and its I/O pattern is different from a user database:

Sharing a volume interleaves those, so a large sort spilling to tempdb slows every read the application needs. On a busy instance that is measurable.

On modern shared storage the performance half weakens, the same way it does for the data and log separation argument. On a SAN, two drive letters may be the same physical devices. The capacity argument does not weaken at all, because it is about which files share a free space pool.

And tempdb is the one database that genuinely benefits from local fast storage. It does not need to be on resilient shared storage, because it is recreated at every restart and never needs recovery. A local NVMe volume for tempdb is one of the cheapest performance improvements available on a virtualized instance, and it solves this finding at the same time.

How to confirm it yourself

Where everything lives:

SELECT CASE WHEN mf.[database_id] = 2 THEN 'tempdb' ELSE DB_NAME(mf.[database_id]) END AS [database_name],
       mf.[name]                                      AS [logical_name],
       mf.[type_desc],
       LEFT(mf.[physical_name], 3)                    AS [drive],
       CAST(mf.[size] * 8.0 / 1024 AS DECIMAL(12,1))  AS [size_mb],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[database_id] = 2 OR mf.[database_id] > 4
 ORDER BY [drive], [database_name], mf.[type_desc];

Group by the drive column. Any drive holding both tempdb files and user data files is the finding.

Free space on that volume, which is the number that matters:

SELECT DISTINCT
       vs.[volume_mount_point],
       vs.[logical_volume_name],
       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
 ORDER BY [free_gb];

How large tempdb actually gets, which tells you what headroom it can consume:

SELECT [name],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1))  AS [current_mb],
       CASE WHEN [max_size] = -1 THEN 'unlimited'
            ELSE CAST([max_size] * 8 / 1024 AS VARCHAR(20)) + ' MB' END AS [max_size]
  FROM tempdb.sys.database_files WITH (NOLOCK)
 ORDER BY [type], [file_id];

Unlimited growth on a shared volume is the combination that produces the outage.

Whether the I/O is actually contending:

SELECT LEFT(mf.[physical_name], 3)                                  AS [drive],
       CASE WHEN vfs.[database_id] = 2 THEN 'tempdb' ELSE 'user' END AS [which],
       SUM(vfs.[num_of_reads])                                      AS [reads],
       SUM(vfs.[num_of_writes])                                     AS [writes],
       SUM(vfs.[io_stall]) / NULLIF(SUM(vfs.[num_of_reads] + vfs.[num_of_writes]), 0) AS [avg_stall_ms]
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
 INNER JOIN sys.master_files AS mf WITH (NOLOCK)
         ON mf.[database_id] = vfs.[database_id] AND mf.[file_id] = vfs.[file_id]
 GROUP BY LEFT(mf.[physical_name], 3),
          CASE WHEN vfs.[database_id] = 2 THEN 'tempdb' ELSE 'user' END
 ORDER BY [drive];

How to fix it

Move tempdb to its own volume. It is the easiest database to move, because it is recreated at startup.

USE [master];
GO

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev',  FILENAME = N'T:\TempDB\tempdb.mdf');
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev2', FILENAME = N'T:\TempDB\tempdb2.ndf');
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev3', FILENAME = N'T:\TempDB\tempdb3.ndf');
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev4', FILENAME = N'T:\TempDB\tempdb4.ndf');
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog',  FILENAME = N'T:\TempDB\templog.ldf');

Then restart SQL Server. The change is recorded now and applied at the next startup, when tempdb is recreated at the new location. You do not copy the files, which is what makes this easier than moving any other database.

Before restarting, three things to check:

1. The folder exists and the service account has full control of it. If it does not, SQL Server will not start, and recovering from that means starting with minimal configuration.
2. The paths are correct. Read the ALTER statements back before the restart:

SELECT [name], [physical_name] FROM sys.master_files WHERE [database_id] = 2;

3. You have console access, in case the restart does not go as planned.

After the restart, delete the old files. SQL Server leaves them behind, and reclaiming that space on the data volume is part of the point.

Size tempdb properly while you are there, since you are taking a restart anyway:

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev',  SIZE = 8GB, FILEGROWTH = 512MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev2', SIZE = 8GB, FILEGROWTH = 512MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev3', SIZE = 8GB, FILEGROWTH = 512MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev4', SIZE = 8GB, FILEGROWTH = 512MB);

Give tempdb the whole volume. Nothing else should be there, so there is no reason to leave free space. Sizing the files to fill the drive means tempdb never autogrows, which removes the growth findings at the same time.

On a virtual machine or a cloud instance, put tempdb on local fast storage if it is available. It needs no resilience, because it is rebuilt at every startup, so ephemeral local NVMe is ideal and usually much faster than the shared storage the data files are on. Note that the folder must be recreated on the ephemeral volume before SQL Server starts, which usually means a startup script.

If you genuinely cannot give tempdb its own volume, set a MAXSIZE on the tempdb files. That bounds the damage: a runaway query fills tempdb and fails, instead of filling the volume and taking every database on it down:

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

That is a deliberate trade, choosing a failed query over a server wide outage, and it is the right one.

How long it takes

About three hours including the change window, most of it the coordination. The restart is the only downtime, and no files are copied.

Related reports

Related checks

Frequently asked questions

Do I have to copy the tempdb files when moving it? No. TempDB is recreated from its configuration at every startup, so you set the new paths, create the folder, restart, and delete the old files afterwards.

Does tempdb need resilient storage? No. It is rebuilt at every startup and never needs recovery, which makes local fast storage ideal and often much faster than the shared storage the data files use.

What if the new folder does not exist when SQL Server starts? It will not start. Create the folder and grant the service account full control before the restart, and keep console access available.

We cannot provision another volume. Set a MAXSIZE on the tempdb files. A runaway query then fails its own query instead of filling the volume and stopping every database on it.