Database in RECOVERY PENDING state - Access Denied
-
Hi
I have s SQL2016 instance with 8 out of 10 databases in recovery pending state and I need assistance.I had just installed an instance of SQL 2017 and restarted the server. Error log says OS error 5 (Access Denied). I restarted the SQL services and it has not helped.
I have tried taking them offline and back on but it doesn't help, it goes back to Recovery Pending.
I have tried:
ALTER DATABASE DBname SET EMERGENCY;
GO
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\Databases\STR_Main.mdf". Operating system error 5: "5(Access is denied.)".
ALTER DATABASE DBname set single_user
GO
ALTER DATABASE (DBname , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;DBCC CHECKDB (
GO
ALTER DATABASE DB DBname set multi_user
GO
ALTER DATABASE DBname SET ONLINE
GO
All 10 databases are in the same folder on the E drive.
I tested creating a new database and got this error:
MSSQL$MSSQLSERVER2016 has permissions to the 2 databases that are working ok but not those that are in recovery pending.
I have tried to give permissions to this account but it doesn't recognise it.Anybody seen this before or have any advice?
Thanks
Ange -
Could you elaborate why these databases would be in recovery pending state ?
Apparently the SQLServer service account cannot access the folder E:\DatabasesJohan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi Johan
Why is what I am trying to figure out. I don't know why they are stuck at recovery pending.
Yes correct it does appear that the service account no longer has access, I'd like to know why? How can this have changed all of a sudden.
Additionally, how come it has access to some of the databases and not others in the same folder?
I am trying to give MSSQL$SQLSERVER2016 permissions to that folder but it is not recognising the service. (I have selected the server as the location below)Thanks
Ange -
For the benefit of anyone that comes across this thread and is experiencing the same issues...
For some, yet unknown, reason the SQL virtual service account NT SERVICE\MSSQL$MSSQLSERVER2016 lost permissions to the databases.
My mistake in my last post was that I didn't include the "NT SERVICE\" part. I had to manually give permissions to this account for each mdf and ldf file.
I then ran then below and they all came back up no problem.ALTER DATABASE DBname SET EMERGENCY;
GO
ALTER DATABASE DBname set single_user
GO
ALTER DATABASE (DBname , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;DBCC CHECKDB (
GO
ALTER DATABASE DB DBname set multi_user
GO
ALTER DATABASE DBname SET ONLINE
GOIf anyone ever finds a reason why the service account would lose permissions like that please do let me know.
-
" ... just installed an instance of SQL 2017 and restarted the server.... "
chances are, that may have reset the folder / file permissions.Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
To the best of my knowledge permissions aren't just lost. Someone with appropriate privileges may have made some changes not realizing what affect those changes may have had.
-
Lynn, I would have said the same however I am the only user accessing this server.
Johan, Quite possible I guess. I've installed multiple of instances in same set up but never experienced this before.
Lets hope I never do again 🙂Thanks both for your input.
- angelinemarren - Monday, January 7, 2019 8:40 AM
You may be the only user accessing the server but that doesn't mean GPO pushed out to your server might have caused the issue. Again, I haven't heard of permission just getting lost. Something causes them to go away.
-
The reason of getting error 5120 is that MDF file are not located at their accurate location. It might be the possibility that system drives might not be having permission to store server file within them. Take a look on troubleshooting the error SQL error 5120:
https://quickdata.org/blog/operating-system-error-5-sql-server-error-5120/ - Lynn Pettis - Monday, January 7, 2019 9:22 AM
Very good point. Given that I rebooted the server it likely picked up a GPO that was waiting to be applied. Will speak to IT about that thank you!
-
I've see it quite often. I think it most likely that the SQL Server Service tried to come up before the drive connections were up.
If you go to SERVICES and lookup the SQL Server service, you can change the Startup Type from Automatic to Automatic(Delayed Start) and that should prevent future occurrences of this problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, January 8, 2019 10:02 PM
That's an interesting thought, thanks Jeff. Sounds like the most likely answer. Going to see if I can cause it to prove it 🙂
- angelinemarren - Wednesday, January 9, 2019 4:27 AM
It also explains why some databases came up fine... the drives were finally available.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Whenever a database is detached, the permission of the database files is modified. If any SQL Login has detached the database, then permissions are given to SQL Server Service account. If any Windows account has detached the database, then the permission is given to the account who has detached itself.
- Right-click on the file which is mentioned in the error message and go to properties.
- In the Properties dialog box, go to Security tab.
- In the Select Group or user names box (which has sysadmin in above image). Click the Edit button and enter the name you used to login to SQL Server. If its windows account, then use that account else use service account.
- Click Check Names and verify that the name you are specifying does exist as SQL Server login.
- Click OK.
- In the Group or User Names box, select the user name you selected in the above step and give full control permission to the user. Click OK.
You can also check this blog: https://bit.ly/2H4zzbo
SQL Database Recovery Expert 🙂
-
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply