Request More Information

We are hiring!

Xtivia, Inc.
2013 North America Liferay Partner of the Year

Join our top-notch team of Enterprise Java and Liferay Portal professionals in our Austin, TX office or work remotely! Please check out our current Dice listings.

Recent Bloggers

Chris Shaw
Posts: 7
Stars: 0
Date: 9/17/14
Sushil Shirodkar
Posts: 1
Stars: 0
Date: 8/28/14
Luke Smith
Posts: 16
Stars: 0
Date: 8/27/14
Vamshidhar Srikantapuram
Posts: 11
Stars: 0
Date: 8/8/14
Barrie Shaw
Posts: 6
Stars: 0
Date: 8/4/14
Omar Yimaier
Posts: 4
Stars: 0
Date: 8/1/14
Bob Dietrich
Posts: 8
Stars: 0
Date: 8/1/14
Matt Wolinski
Posts: 2
Stars: 0
Date: 7/30/14
Richard Ngo
Posts: 1
Stars: 0
Date: 6/19/14
Kaushik Nagaraj
Posts: 8
Stars: 0
Date: 5/22/14
« Back

How to start SQL Server if you lost TEMPDB Data files?

 

Recently we migrated one of our client's SQL Server instances to a new server. During the migration we had moved the storage to the new server so the old server didn't have some of the disk drives that were previously mounted on it.

 

After the migration the client requested that we bring up the old SQL Server instance for some testing. This was not requested by the client during the planning phase so we were not prepared for this. Unfortunately, the disk drive that was used to store the data files for TEMPDB was now mounted on the new server. So I could not start the SQL Server instance because of the following errors 

 

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

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

 

FCB::Open failed: Could not open file C:\Data\tempdb\datatempdb.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 "C:\Data\tempdb\datatempdb.mdf". Operating system error 3: "3(The system cannot find the path specified.)".

Error: 1802, Severity: 16, State: 4.

 

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 'C:\Data\tempdb\datatempdb.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.

 

So we could not start the TEMPDB and there was no option to mount the drive back.

There is a '-f' startup option that you can use to start SQL Server with minimal configuration. This does put the SQL Server instance in single user mode. So starting SQL Server using the command line was the way to go.

 

Following are the steps needed to add a new file to TEMPDB and then restart SQL Server.

1)   Open command prompt window #1 as ADMIN and go to the BINN directory where SQL Server is installed. This is typically in

 

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

OR

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

 

2) Then execute command like

       Sqlservr.exe /f /c  

 

3)   Then open one more command window #2 and if this is a default instance then open SQLCMD  using the following command

SQLCMD –S localhost –E

 

4)   This will open a SQL command prompt there where you can type the following commands

      

1> USE MASTER

2> GO

3> ALTER DATABASE tempdb MODIFY FILE

4> (NAME = tempdev, FILENAME = 'C:\NEWPATH\datatempdb.mdf')

5> GO 

6> quit

 

 

 

5)   Now go back to Command window #1 and hit CTRL C.

It will ask if you want to stop the instance. Y/N. 
Enter Y

6)   Now start the SQL Server instance from configuration manager. You should see the new data file created and SQL Server started.

 

Comments
Trackback URL:

Join the discussion! Speak your mind! Be the first.

Request More Information