Today we will learn how to fix TempDB error: CREATE FILE encountered operating system error 3 (The system cannot find the path specified.) while attempting to open or create the physical file ‘D:\TempDB.mdf’.
During Comprehensive Database Performance Health Check we do various checks for performance and according to that, we create a checklist of activities. During a recent consulting engagement, we have found a TempDB as a performance bottleneck for a client. The disk on which TempDB was deciding was an extremely slow disk. The advice was to move the TempDB to a faster drive according to this blog post: Moving TempDB to New Drive – Interview Question of the Week #077 and Move TempDB for Performance – SQL in Sixty Seconds #107. However, my client made a small typo in pasting the drive letter and SQL Server started to give errors related to TempDB.
Well, this is very easy to fix it. Let us see how we can fix it.
Step 1: First of all – login to the Operating System where you have installed SQL Server and find the location of the drive where SQL Server is installed. It is usually in the folder of Program Files. Once you locate the SQL Server folder look for the Folder BINN.
Step 2: Once you find the folder BINN, open Command Prompt navigate to the folder BINN and run the following command. This will start SQL Server with minimal configuration.
[script]Sqlservr.exe /f /c[/script]
Step 3: Now open another Command Prompt Window. Run the following command to start SQL Server in single-user mode.
[script]SQLCMD –S localhost –E[/script]
Step 4: The next steps are very easy. Type the command to relocate the TempDB to your preferred location.
[script]USE master;
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = ‘E:\CorrectPath\datatempdb.mdf’);
GO[/script]
That’s it. Now you can quit the SQLCMD session by typing CTRL+C.
Step 5: Restart the SQL Server service and you should be able to get going.
If you still face a problem, just leave a comment and I will be happy to help by replying to the comments to fix encountered operating TempDB errors.
Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series. I hope you appreciate learning about the last few SQL in Sixty Seconds Video.
- Performance: Between and Other Operators – SQL in Sixty Seconds #191
- Most Used Database Files – SQL in Sixty Seconds #190
- Optimize DATE in WHERE Clause – SQL in Sixty Seconds #189
- Data Compression for Performance – SQL in Sixty Seconds #188
- Get Current Time Zone – SQL in Sixty Seconds #187
- Detecting Memory Pressure – SQL in Sixty Seconds #186
- CPU Running 100% – SQL in Sixty Seconds #185
- Generate Script of SQL Server Objects – SQL in Sixty Seconds #184
- Prevent Unauthorized Index Modifications – SQL in Sixty Seconds #183
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
If you have any questions, you can always reach out to me on Twitter.
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
