4

I am trying to bring a SQL Server database online and accessible that is in Recovery Pending state. The SQL Server service uses an Active directory account that is a domain administrator and a member of sysadmin on sql server. The SQL Server account should have rights in the SQL file folders as it is a domain admin. It is however not a local administrator in the SQL Server.

The error messages are:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBName.ndf". Operating system error 5: "5(Access is denied.)". Msg 5120, Level 16, State 9, Line 1 Unable to open the physical file "F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBName.ndf". Operating system error 5: "5(Access is denied.)".

While

alter database [DBName] set online;

results in the error below:

Msg 945, Level 14, State 2, Line 1 Database 'databs' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

Can you think of a solution?

  • 1
    You have to give proper permissions to sql agent account. Until then, it cannot access the files and so you can't bring the database online. – Kin Shah Nov 5 '14 at 23:04
  • @KIN why would rights be required by SQL Server agent account. @ User42148 please make sure SQL Server service account has read write permission on Folder where ndf file resides. If things do not turn out please for a time being add SQL account as local admin and try – Shanky Nov 6 '14 at 0:13
  • @shanky we both are telling same things. – Kin Shah Nov 6 '14 at 4:03
  • The ndf file is the same location as the mdf file. I only get this error on the ndf file. The SQL Service Service is domain admin. Imagine that just to pass by the problem. I could not add this account as a local administrator on the server. What is tricky is the server state is recovery pending and why would only one ndf file gives that error while the other mdf and ldf file are still on the same folder. Could it be possible the ndf file is corrupt? – user42148 Nov 6 '14 at 20:02
  • Your Msg 945 says " See the SQL Server errorlog for details". Anything in there of note? – Michael Green Apr 15 '15 at 11:08
2

The first issue was someone denied the SQL Server Services in the NDF and MDF files and once that denied was removed and granted appropriate access, the first issue was resolved. The second solution was that corrupt NDF file had no data so I removed it and database was back online.

0

In my case problem was that database wasn't even there. It turns out that systems personnel was looking to free up some disk space so they wiped some of dev databases. So:

  • first step should be to check if database is actually there, (In your case F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\)
  • then you should check if SQL account has access to that location (As you and SQL server might have different permissions)
  • and then lastly check for (your) permissions to alter that specific database.
-1

please check user rights for that drive. May be problem is with access rights for particular drive. Give full rights for that drive with 'everyone' user. Your problem will sort out. Restart Sql server instance and check database status, your recovery pending error is solved. Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.