SQL SERVER – FIX: CREATE FILE Encountered Operating System Error 5 (Access is Denied.)

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.

SQL SERVER - FIX: CREATE FILE Encountered Operating System Error 5 (Access is Denied.) errorfile

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)

SQL Error Messages, SQL Server, Windows
Previous Post
SQL SERVER – Upgrade Blocked: The Specified Edition Upgrade is Not Supported
Next Post
SQL SERVER – How to DROP or DELETE Suspect Database?

Related Posts

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!

    Reply
  • 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

    Reply
  • Hi Pinal,

    Thanks for this, it worked perfectly for me and saved lot of time.

    Regards,
    Wil

    Reply
  • 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

    Reply
  • Just the trick! thanks!!

    Reply
  • 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.

    Reply

Leave a Reply