Once there was an urgent request from my client and they asked my assistance. Without wasting any time, I sent them GoToMeeting invite and started working. In this blog we would learn how to fix FCB:: Open failed for TempDB database.
As soon as I joined the call with them, they explained that they were not able to start SQL Service. The first step in such situation is to look at SQL Server ERRORLOG. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
Here are the messages at the end of the log file. I have added line number and done some more formatting for clarity.
- Clearing a tempdb database.
- 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:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\tempdb.mdf’.
- Error: 17204, Severity: 16, State: 1.
- FCB:: Open failed: Could not open file T:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\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:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\tempdb.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.
- 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.
- SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
Here are my earlier blogs with “similar” errors.
SQL SERVER – FIX: Msg 5123, Level 16 – CREATE FILE Encountered Operating System Error 5
SQL SERVER – FIX: CREATE FILE Encountered Operating System Error 5 (Access is Denied.)
In an earlier blog, it was just a permission issue which can be fixed, and SQL can be started. But, in the current situation, my client lost T drive and there is no way to get it back. So, we need to ALTER the TempDB configuration and point to an existing location. You can see the problem now, to ALTER the path, we need to start SQL Server, but SQL is not staring because the path is incorrect. Chicken and Egg situation!
We need to start SQL Server with a minimal configuration using startup parameter f. Here is what I generally do in such situations.
- Start SQL Service via command prompt using NET START command. We also need to provide parameter “f” and “mSQLCMD” . This will make sure that other than SQLCMD, no application is able to connect to SQL Server. This is to avoid errors like below.
Login failed for user. Reason: Server is in single user mode. Only one administrator can connect at this time.
NET START MSSQLSERVER /f /mSQLCMD
Make sure SQLCMD is in all upper case and “f” and “m” are lowercase. You need to use the correct service name. MSSQLSERVER is for the default instance. For named instance, it would be MSSQL$INSTANCENAME
Once we start it, below is the message in SQL Server ERRORLOG.
Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically. After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
Above is perfect!
- Now, we would connect SQL using SQLCMD and run ALTER DATABASE for tempdb to fix the incorrect path. From the command prompt, we need to run below.
SQLCMD -S <ServerName> -E
Here are the ALTER commands which you need to modify based on your environment.
ALTER DATABASE tempdb Modify FILE (Name = 'tempdev',FileName = 'C:\tempDB\tempdb.mdf')
ALTER DATABASE tempdb Modify FILE (Name = 'templog',FileName = 'C:\tempDB\templog.ldf')
Here is the screenshot for my server.
- After this, we need to stop SQL Services and start normally.
Have you been into such a situation and found some other solution to this error? Please share via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Great Scripts indeed!
u are a savior :)
you are a savior bro :) east or west Pinal is the best
I have a similar problem. I am able to execute the ALTER DATABASE commands to change the path for tempdb however. the MDF, NDF, and LDF initial file sizes are large and I don’t have any disk with sufficient free space to re-create the tempdb files. I attempted to change the initial size with the ALTER DATABASE command however, as soon as I attempt to run this command, the engine responds with “Error: 17053, Severity: 16, State: 1.
E:\…..\MSSQL\DATA\tempdb.mdf: Operating system error 112(There is not enough space on the disk.) encountered.
Error: 5173, Severity: 16, State: 1.
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.”
As if it ignores my resize statement and is still attempting to use the larger original tempdb files size.
Thanks Pinal , I was running in a issue; it helped me out. Thanks once again dear.
Thanks a lot!
It helped me for an similar issue.
Wow, you saved my day, thank you so much for sharing the workaround in very easiest way.