Quick Scan Report – TempDB on the C Drive

What this check looks for

Any tempdb file whose physical_name begins with c, meaning it lives on the system drive. Both data files and the log file are included.

Why it matters

This is not really a SQL Server problem. It is a Windows problem waiting to happen.

Tempdb is the most volatile database on the instance. It absorbs sorts, hashes, spills, table variables, temporary tables, row versions, online index rebuilds and cursors, and its size depends on what the workload is doing at that moment rather than on how much data you store. One query with a bad estimate can add tens of gigabytes to it in minutes, and there is no warning beforehand.

When that happens on the C: drive:

  • Windows runs out of room. Not SQL Server, Windows. The page file cannot extend, the event logs cannot write, Windows Update fails, and the server gets into a state where administrators cannot log in to fix it.
  • The server may not restart cleanly. A system drive with no free space is one of the reliable ways to turn a recoverable incident into a rebuild.
  • The failure lands on the wrong team. It looks like an operating system fault, because it is one, and the database that caused it is not the obvious suspect.

There is a performance argument too, and it is real but secondary. The system drive is usually the slowest storage in the machine, it is shared with the operating system and the page file, and tempdb is frequently the busiest database on the instance. But the reason this check exists is the outage, not the throughput.

How to confirm it yourself

SELECT [name]                                    AS [logical_name],
       [type_desc],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb],
       CASE WHEN [is_percent_growth] = 1
            THEN CAST([growth] AS VARCHAR(10)) + ' %'
            ELSE CAST([growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       [physical_name]
  FROM tempdb.sys.database_files WITH (NOLOCK)
 ORDER BY [type_desc], [name];

How much room the drive actually has:

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]
  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

Moving tempdb is one of the easier file moves, because tempdb is recreated at every restart. You tell SQL Server where the files should be, and it creates them there the next time it starts. The old files are left behind and you delete them by hand.

USE [master];
GO

ALTER DATABASE [tempdb]
  MODIFY FILE ( NAME = N'tempdev', FILENAME = N'D:\SQLTempDB\tempdb.mdf' );

ALTER DATABASE [tempdb]
  MODIFY FILE ( NAME = N'templog', FILENAME = N'D:\SQLTempDB\templog.ldf' );

-- and one line per additional data file: tempdev2, tempdev3 and so on

Then, in order:

  1. Create the target folder and give the SQL Server service account full control of it. Getting this wrong means SQL Server does not start, so check it before restarting.
  2. Restart the SQL Server service. The change does not take effect until then.
  3. Confirm with the query above that the files are where you expect.
  4. Delete the old files from C:. Nothing does this for you, and they are still occupying the space you were trying to free.

While you are there, this is the natural moment to fix tempdb’s other settings, because you are restarting anyway: the number of data files, sizing them all the same, and a sensible fixed growth. Each of those has its own check in this report.

Choose the destination deliberately. Tempdb is a good candidate for the fastest storage in the machine, and local SSD is legitimate even in a cluster, because tempdb does not need to survive a failover.

How long it takes

About two hours, almost all of which is arranging the restart.

Report Why you would go there
TempDB Allocation How tempdb is laid out and sized today.
Disk Space Free space per volume, including the one you are moving to.
I/O by Drive Which drive is actually fastest, before you choose.
TempDB Consumers What is using tempdb, which sets how much room it needs.
Files Every file on the instance and where it lives.
Check
TempDB only has a single data file Worth fixing in the same restart.
TempDB sized differently Also worth fixing in the same restart.
Data and log files on the same drive The same placement question for user databases.
User databases on the C: drive The same problem for data rather than tempdb.
Low disk space What this becomes when tempdb has a bad day.

Frequently asked questions

The C: drive is large and has plenty of room. Tempdb’s size is set by what the workload does, not by what you planned. A single bad plan can consume any amount of headroom, and the consequence of filling C: is losing the operating system rather than losing a database.

Only the log file is on C:. It is the same problem. The tempdb log grows under a long transaction just as the data files grow under a large sort.

Can I move tempdb without a restart? No. The ALTER DATABASE statement records where the files should be, and the files are created there at the next startup.

Should tempdb go on the same drive as the user data files? Better than C:, but its own volume is better again. Tempdb’s I/O pattern is different from a user database’s, and separating them stops one starving the other.