SQL Server re-creates tempdb on predefined folders each time sql server service starts.
In the article “How To Move The Tempdb Database To Another Disk“, you can see how to determine the disks where tempdb will be created when sql server service starts.
I think it would be better understood with the help of an example. First, let’s run the following script on instance without a folder named Tempdb under the C disk.
|
1 2 3 4 5 |
use master go alter database tempdb modify file (name = tempdev, filename = 'C:\Tempdb\tempdb.mdf') go alter database tempdb modify file (name = templog, filename = 'C:\Tempdb\templog.ldf') |
Since there is not a folder named Tempdb under the C disk, the above script will receive the error as follows.
Msg 5121, Level 16, State 1, Line 3
The path specified by “C:\Tempdb\tempdb.mdf” is not in a valid directory.
Msg 5121, Level 16, State 1, Line 5
The path specified by “C:\Tempdb\templog.ldf” is not in a valid directory.
Under the C disk, create a folder named Tempdb and run the same script again. Set tempdb to be created in the Tempdb folder under the C disk in the next sql server service restart.
Then delete the Tempdb folder that we created.
Thus, when you restarts the SQL Server service, the service will not be opened because it cannot find the folder in which to create tempdb.
We then restart SQL Server Service through SQL Server Configuration Manager.
SQL Server will not start.
When you look at the errors in the Application tab at the bottom of Windows from the Eventviewer, you will see an error like the following.
To access the Eventviewer, you must type eventvwr from Search.
FCB::Open failed: Could not open file C:\Tempdb\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
If you re-create the Tempdb folder under the C disk and start the service, the service will start.
If you don’t have a chance to create a folder under the C disk, you should start the sql server service with the -f startup parameter.
You can find out how to start SQL Server with the -f startup parameter in the article “SQL Server Startup Parameters“.
Make sure that SQL Server Agent Service is turned off before starting the service.
Because with the -f parameter, sql server will work in both minimum configuration and single user mode.
This means that only one user can connect to a sql server at a time.
If “SQL Server Agent” Service is turned on, you may be unable to connect because it tries to connect.
Then we open SSMS and close the Connect box by clicking Cancel and we click New Query.
After clicking New Query, we set the new path of tempdb by pasting the following script to the screen where the query will be connected.
Thus, when the service is opened again, tempdb will be created in the path that we specified in this script and the problem will be solved.
You must then restart the service by removing the -f parameter.
|
1 2 3 4 5 |
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') |
An important point to note here is that you cannot do this if you do not cancel the first incoming connection screen when you turn on SSMS.
In such a case, you must perform the same operation by restarting SSMS.
You will get an error as follows. You should do it when I tell you everything.
Otherwise you will get the error as follows.
In Error, it tells us to; SQL server is running in single user mode, so only one administrator can be connected to the system at the same time.
Error:
Cannot connect to
Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time.(Microsoft SQL Server, Error: 18461)
