SQL Interview: 113 Missing tempdb files

SQL Interview: 113 Missing tempdb files

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Advanced

Question:

You have a SQL Server server attached to multiple storage volumes. One of the volumes has been lost. It contained the files for the tempdb database.

You know that tempdb is recreated on each restart of SQL Server, but will SQL Server still start when the files are missing? Will it just automatically create new files? If not, how could you work around it?

Answer:

No, SQL Server will not start normally if the tempdb database files are missing from their configured location because it is a critical system database required for operation.

tempdb is essential and it is recreated every time the SQL Server service restarts. If the tempdb files or the directory are missing, the service will fail to start and record an error in the SQL Server error log, typically mentioning an “operating system error 3 (The system cannot find the path specified.)” when trying to open the files.

How to Resolve the Issue

You can resolve this by starting the SQL Server instance in minimal configuration mode, which bypasses the normal startup process and allows you to modify the tempdb file locations to a valid path.

  1. Start SQL Server in minimal configuration mode: Open a command prompt and use the -f flag to start the instance with minimal configuration. You can also add the -mSQLCMD flag to ensure only the sqlcmd utility can connect and “hijack” the single-user connection.
sqlservr.exe -f -mSQLCMD

(Note: The exact command might vary slightly depending on your instance name. 2. Connect using sqlcmd: From the same command prompt window where you started the server, connect using sqlcmd.

sqlcmd -S YourServerName\YourInstanceName -d master
  1. Alter the tempdb file locations: Run T-SQL commands to point tempdb to a valid, existing file path (e.g., E:\SQLData\ and F:\SQLLog\ in the example below).
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf' );
GO
ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf' );
GO

Run a query on sys.master_files to verify the new locations are recorded in the master database.

  1. Stop and Restart SQL Server: Stop the SQL Server instance (you can usually do this by pressing Ctrl+C in the command prompt or stopping the service via the SQL Server Configuration Manager). Then, restart the SQL Server service normally via the SQL Server Configuration Manager. The server will now use the new locations and automatically recreate the tempdb files.

2026-04-14