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?
- Recreate (Move) SQL Server TempDB Database in the Single-User Mode
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
Note. The minimal configuration mode is only used to fix configuration errors. After making the changes, restart your server without the –f option.
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:DBTempdb.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.
- Log in to the host with the MSSQL instance you need (locally or using RDP);
- 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 /fcommand 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,
-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 mssql1node2 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:
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:tempdb.mdf');
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:templog.ldf');
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.
Note. I place tempdb on this disk temporary, since you are likely to change the tempdb configuration. You can split it into several data files and modify the path to it after repairing the MSSQL Server.
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 FilesMicrosoft SQL ServerMSSQL15.NODE2MSSQLBinnsqlservr.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.