MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter












































How to Start SQL Server without TempDB Database or with Minimal Configuration

Read Comments   |   Related Topics: SQL Server Disaster Recovery Tips > Next Topic: SQL Server Indexing Articles & Tips


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.

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 then 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."

Related Tips

Different Ways to Retrieve SQL Server Configuration Details
How to Move TempDB Database Files to a New Drive in SQL Server
Improved Startup Parameters in SQL Server 2012
Steps to Connect to SQL Server When all System Administrators are Locked Out
How to Repair a Suspect Database in SQL Server
How to Connect to a Named Instance of SQL Server
Introduction to SQL Server Configuration Manager
SQL Server Disaster Recovery Tips for DBAs and Developers

How to Start SQL Server with Minimal Configuration / How to Start SQL Server without TempDB database

 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

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

SQLCMD –S localhost –E

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

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

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

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

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

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.

More... Disaster Recovery Tips for DBAs and Developers







Last Updated On: May 02, 2016



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: