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