Why do the databases go into the "recovery pending" state when the share permissions are changed from "everyone"?

Question

Yesterday we had a power failure and after the power was back our financial databases on the SQL did fall into "Recovery Pending" state. After struggling a while I've remembered that a few days ago we did change the sharing permissions of the database directory to certain users from "everyone" and tried to revert the permissions back to "everyone" and everything was OK again in a few minutes.

What reminded me of this directory share permission change were these error messages in the event log:

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file '\\SERVER\xxx.ldf'. Diagnose and correct the operating system error, and retry the operation.

FCB::Open failed: Could not open file \\SERVER\xx._MS for file number 1. OS error: 5(Access is denied.).

Why did this happen?

Answer 1

It sounds like a simple permissions issue.

Why did this happen?

The Everyone permission literally allows anyone to connect to those network shares and access those files. When you remove those permissions, now no one is allowed to access them. What may be confusing is that you may think that SQL Server has access to those files and shares, when, in fact, it does not.

To allow specific access to those databases that run over SMB shares (starting with Microsoft SQL Server 2012), Microsoft recommends that the SQL Server service run as a <domain name>\<user name> and that this account have FULL CONTROL to the file share AND NTFS permissions to the underlying files and/or directories within that file share.

Alternately, Microsoft recommends that if you use a system account instead for the SQL Server service account then apply FULL CONTROL share AND NTFS permissions to the account <domain_name>\<computer_name>$--where computer name is the computer name of your SQL Server.

I'd retry removing those "everyone" permissions once more, in a test environment, to validate that this fixes your issue.

For more details see:

https://msdn.microsoft.com/en-us/library/hh759341.aspx

Answer 2

Can also be the Auto_Close database option if turned on.

I've experienced several clients databases where "recovery pending" was happening weekly/fortnightly, but not necessarily due to power cuts. For us it was the Auto_Close option of the database. Make sure it is false or off particularly if the database is used frequently. link

It turns out that we were installing SQL Express for these small clients and Auto_Close was on by default. Best practice is to turn it off.

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET AUTO_CLOSE OFF;
GO