One of my existing clients performed migration of disk in SQL Server failover cluster. After completing their planned activity, they found that SQL Server was not coming online in the cluster. In this blog, we would learn how to fix error CREATE FILE encountered operating system error 5(Access is denied.) on TempDB during SQL Server startup process.
When they contacted me, I captured a few more details which helped in finding the right corrective action. They had 2 node cluster. They added a new disk, swapped drive letters and copied-pasted all the SQL Server files there. After that, they tried bringing the SQL Server resource online. I captured a snippet of the ERRORLOG file.
2018-08-25 22:49:04.31 spid6s CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘T:\TEMPLOGS\templog.ldf’.
2018-08-25 22:49:04.32 spid6s Error: 5123, Severity: 16, State: 1.
2018-08-25 22:49:04.32 spid6s CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘T:\TEMPDATA\tempdb.mdf’.
2018-08-25 22:49:04.33 spid6s Error: 17204, Severity: 16, State: 1.
2018-08-25 22:49:04.33 spid6s FCB::Open failed: Could not open file T:\TEMPDATA\tempdb.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
2018-08-25 22:49:04.33 spid6s Error: 5120, Severity: 16, State: 101.
2018-08-25 22:49:04.33 spid6s Unable to open the physical file “T:\TEMPDATA\tempdb.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
2018-08-25 22:49:04.33 spid6s Error: 1802, Severity: 16, State: 4.
2018-08-25 22:49:04.33 spid6s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2018-08-25 22:49:04.33 spid6s 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.
From above we can clearly see that SQL Server is not able to create MDF and LDF file for TempDB database. Yes! It’s a Windows permissions issue. As we know SQL Server service account would be used to create the new files for TempDB database and clearly since they swapped the drive letters, they didn’t take care of the permission. Since the files are not created, we are seeing “OS error: 2(The system cannot find the file specified.).” subsequently.
WORKAROUND/SOLUTION
We need to note the folder in which SQL is trying to create the files. You need to check ERRORLOG in your environment. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
As per messages in sample ERRORLOG above, it is T:\TEMPLOGS\ and T:\TEMPDATA\.
- Right click on the folder, go to Properties > Security Tab > Edit Button > Add Button.
- Add domain user/local user/group under whose account SQL services are running as. Make sure you provide Full Control to it.
- Keep hitting OK to apply the changes.
If you are not aware of the steps to find service account for SQL Server then below blog should help. How to Find Service Account for SQL Server and SQL Server Agent? – Interview Question of the Week #179
Hopefully, this blog would help someone, who is not an SQLDBA, to fix this issue.
Reference: Pinal Dave (https://blog.SQLAuthority.com)
8 Comments. Leave new
Worked perfectly for me! Thank you so much! Was stuck on this issue for a while and the only other alternative was to download a large sized update by Microsoft!
Thanks for letting me know Arjun.
Hi Pinal,
I have 2 named instances, I changed only the service account for both the instances. One instance came up and other didn’t, do you still think its a permission issue on the disks ?
Below are the errors I found from EventViewer,
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\Data\tempdb.mdf’.
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘L:\Log\templog.ldf’.
FCB::Open failed: Could not open file C:\Data\tempdb.mdf for file number 1. OS error: 5(Access is denied.).
TIA
Hi Pinal,
Thanks for this, it worked perfectly for me and saved lot of time.
Regards,
Wil
After adding security to both mdf and ldf locations i still could not attach the databases.
Closed SSMS and opened as Administrator, which allowed me to attach the databases
thanks ! same here: attaching the MDF+LDP using SSMS openend as Administrator did the trick
Just the trick! thanks!!
Hi, while trying to create database in Visual Studio 2017, with command “Update-Database”, it seems like I can’t access SQL Server to create files. Now, I don’t understand how to make sure I have full access to server and all folders. I tried several tips from web, nothing worked. Now, I know this might be a question for someone else, but I don’t understand how to fix this problem even tho, online I everyone says that it’s account permissions problem.