SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

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.

In a Failover Cluster Instance to Move TempDB read, How to Configure TempDB on Local Disk in SQL Server 2012/2014 Failover Cluster to Improve Performance

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

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.

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning