Starting SQL Server without TempDB Database

In this article, we will show how to repair the TempDB Database on the Microsoft SQL Server using Single User or Minimal Configuration mode. This guide should help you to repair/move the MSSQL temdb database if it is corrupted or missing.

This method is also used in emergency cases when SQL Server cannot be started or you cannot connect to it due to high load or configuration errors preventing SQL Server from starting up normally.

How to Start SQL Server in the Minimal Configuration Mode?

If SQL Server cannot be started due to any configuration errors:

• Tempdb is missing or damaged
• A trigger or procedure error prevents you from connecting to an SQL Server instance
• Any other errors related to SQL Server configuration

Then you have to start SQL Server in the Minimal Configuration mode. To start the MSSQL in Minimal Configuration, add -f option to the instance startup command.

In this mode, MSSQL is started with the specific configuration:

• A server is started in the single-user (exclusive) mode
• Stored procedures configured to start after the server startup are not started
• Tempdb database will be configured to the minimum size allowed
• Database Checkpoints are disabled

In the single-user mode, you can restore the damaged master database and other system databases.

Recreate (Move) SQL Server TempDB Database in the Single-User Mode

As an example, we will take a scenario when tempdb files on a disk are completely lost and SQL Server cannot be started.

If temdb is missing, the following errors will appear in the MSSQL logs:

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘D:\DB\Tempdb.mdf’
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

1. Log in to the host with the MSSQL instance you need (locally or using RDP);
2. Open the SQL Server Configuration Manager, then go to the instance properties -> Startup Parameters and add the -f option;
   You can also use the NET START MSSQLSERVER /f command to start your SQL Server with minimal configuration.
   
3. Restart the SQL Server instance;
   Tip. After the restart of the instance, the server will accept only a single user connection. To prevent anything from using the connection, disable the SQL Server Agent, restrict access to the SQL Server in your firewall, and make sure that other administrators or local apps do not use the connection. You can also use -m parameter to specify the app you are going to use to connect. For example,-mSQLCMD or -m"SQL Server Management Studio". Since you cannot set quoted parameters in the SQL Server Configuration Manager, like -m"SQL Server Management Studio", you will be able to use it only if you start SQL Server from the command prompt.
4. Open the SQL Server Management Studio and click the New Query button.

If you see an error like this, somebody is already using the connection:

Cannot connect to mssql1\node2
Login failed for user username. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

If you did everything right, you will access the server.

Then change the paths to the damaged tempdb. Run the following T-SQL commands:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf');
GO

It will set a new tempdb configuration. The tempdb files will be re-created from scratch on the E:\ drive.

The file tempdev/templog has been modified in the system catalog. The new path will be used the next time the database is started.

Remove -f parameter from your instance configuration in SQL Server Configuration Manager and restart the instance.

Using this mode, you can also fix some other configuration problems, for example, disable a trigger or a procedure.

Also, to start SQL Server in the Minimal Configuration Mode, you can use the command:

"C:\Program Files\Microsoft SQL Server\MSSQL15.NODE2\MSSQL\Binn\sqlservr.exe" –c -f -sNODE2"

It will start the MSSQL NODE2 instance in the Safe Mode. The -c parameter makes SQL Server boot faster when started from the command prompt. You can also use -m"SQL Server Management Studio" to restrict the number of connections from the SQL Studio or -mSQLCMD to connect only from sqlcmd.