How to Start SQL Server without TempDB Database

Recently, after the scheduled maintenance window when the database administrator started the SQL Server it failed to start due to corruption issues with storage subsystem. On further investigation it was identified that the “T Drive” which had TempDB data and log file was corrupt and was preventing SQL Server from starting successfully.

The only option available for us was to start SQL Server without TempDB (i.e., with minimal configuration) and then move the data and log files of TempDB to a new drive on the server.

Detailed Error Message logged in SQL Server Error Log when TempDB is not accessible for SQL Server Database Engine

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while 
attempting to open or create the physical file 'T:\TempDB\TempDB.mdf'.

Error: 17204, Severity: 16, State: 1.

FCB::Open failed: Could not open file T:\TempDB\TempDB.mdf for file number 1. 
OS error: 3(The system cannot find the path specified.).

Error: 5120, Severity: 16, State: 101.

Unable to open the physical file "T:\TempDB\TempDB.mdf". 
Operating system error 3: "3(The system cannot find the path specified.)".

Unable to open the physical file "T:\TempDB\TempDB.mdf". 
Operating system error 3: "3(The system cannot find the path specified.)".

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Error: 5123, Severity: 16, State: 1.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.)
while attempting to open or create the physical file 'T:\TempDB\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.

Learn How to Start SQL Server without TempDB database

How to Solve this?

To resolve this issue DBA will have to start SQL Server instance with minimal configuration and then move the SQL Server TempDB data and log files to a new location. This article demonstrates the steps that one needs to follow to start SQL Server without TempDB database (i.e., with minimal configuration) and to move TempDB file read, How to Move TempDB Database Files to a New Drive.

When to Start SQL Server with Minimal Configuration?

As a database administrator if you are facing configuration issues with SQL Server and it is preventing the SQL Server Database Server from starting, then you can start SQL Server Instance using minimal configuration.

One can use -f startup option, to start SQL Server with minimal configuration and -c startup option shortens the startup time when SQL Server is started from the command prompt.

“Once you have fixed the issue then you must stop the SQL Server Instance and then restart the SQL Server Instance. If you have a named instance then read the following tip to learn How to Connect to a Named Instance of SQL Server.“

Trending SQL Server Disaster Recovery Articles and Tips

• Improved Startup Parameters in SQL Server 2012
• SQL Server Database Engine Service Startup Options
• How to Configure TempDB on Local Disk in SQL Server 2012/2014 Failover Cluster to Improve Performance
• Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide
• How to Fix “BACKUP detected corruption in the database log” Error in SQL Server
• How to Repair Database in Suspect Mode in SQL Server
• How to Start SQL Server without TempDB Database
• Why to Configure Database Instant File Initialization in SQL Server? Advantage
• Different Ways to Enable Dedicated Administrator Connection in SQL Server
• How to Use Dedicated Administrator Connection in SQL Server

Learn How to Start SQL Server with Minimal Configuration

Steps to Start SQL Server without TempDB Database

Step 1. Open Command Prompt as an administrator and then go to the BINN directory where SQL Server is installed and type sqlservr.exe /f /c. On our Production Server SQL Server is installed on the following drive location “E:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2014\MSSQL\Binn\“.

sqlservr.exe /f /c

Step 2. Open New Command Prompt window as an administrator and then Connect to SQL Server Instance Using SQLCMD.

SQLCMD –S localhost –E

Step 3. Once connected to SQL Server Instance execute the below TSQL Script to move TempDB data and log file to a new location. For detailed information and best practices, see How to Move TempDB Database Files to a New Drive in SQL Server.

USE MASTER
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'E:\TempDB\Tempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'E:\TempDB\templog.ldf')
GO

Step 4. Exit SQLCMD window by typing Quit and Press Enter.

Step 5. In the initial window click CTRL C and enter Y to Stop SQL Server Service.

Step 6. Finally, start SQL Server Database Engine Using SQL Server Configuration Manager.

Conclusion

It is highly recommended to document physical location of data and log files for the entire system and user database in SQL Server along with SQL Server Configurations. This will help you quickly recover your database in case of a disaster.