Wednesday, March 7, 2012

Error: 5123 Error: 5120 TEMPDB


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

Recently we migrated one of our 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 the old server.
After the migration we realized that there were couple of server objects that were not scripted out from the old server. So we had to start the SQL Server instance on the old server. 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 start SQL Server using the command line.

Following are the steps needed to add 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 following command
SQLCMD –S localhost –E
4) This will open SQL command prompt there type the following command
Next execute the following command
1> USE MASTER
2> GO
3> ALTER DATABASE tempdb MODIFY FILE
4> (NAME = tempdev, FILENAME = 'C:\NEWPATH\datatempdb.mdf')
5> GO 
6> quit

5) 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.




5 comments:

  1. That's very cool, most useful, Thank you very much.

    ReplyDelete
  2. Hi-

    I just started a SQL class and now have SQL Server 2005 and SQL Management Express. The teacher gave out two databases to attach but I get the following error when trying to attach either database:


    Attach database failed for Server 'xxx\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

    ------------------------------
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

    ===================================

    Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AP_log.LDF". Operating system error 2: "2(The system cannot find the file specified.)". Microsoft SQL Server, Error: 5120)

    ReplyDelete
    Replies
    1. Can you tell me the extension of the files the instructors gave you? If they both are 'mdf' files then you need the 'ldf' files for them. In SQL Server all databases need to have at least two files.
      1) Data file (this has mdf extension)
      2) Log file (this has ldf extension)

      If you have both then copy them to the same directory. Or in your case copy them to
      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

      So you should have two files AP.mdf and AP_log.ldf.

      Then try to attach the data file and it should show you the ldf file in the window.

      Delete
  3. An update. I have sql server 2008 R2 and the instructions didn't work for me (the attach process failed).
    To resolve:
    Run ssms as an administrator and attach, it worked for me.
    Cheers.
    Dhaval

    ReplyDelete
  4. Now users can quickly attach SQL database without facing any error with SQL recovery tool. http://www.sqlrecoverysoftware.net/blog/how-to-fix-sql-error-5171.html

    ReplyDelete