2

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?

  • 3
    Did the SQL Server service account have rights to the share at that moment? If not, this will be the reason why your database files could not be found. – Stijn Wynants Apr 1 '15 at 10:57
3

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

  • Ok, thanks for the tip but I don't have access to that particular server anymore. If they get back to me I'm gonna check these permissions. – Montag451 Jan 15 '16 at 17:43
1

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

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.