Question: What is the Difference between SUSPECT and RECOVERY PENDING?
Answer: Sometimes I get messages on Skype about some quick suggestions/ideas and if I am free, I do help them. One of the blog readers pinged me and said that they have moved all user databases to a new drive and restarted SQL Server and they are in recovery pending state.
I asked him to share ERRORLOG and we found below.
2017-01-25 09:57:22.70 spid20s Error: 5120, Severity: 16, State: 101.
2017-01-25 09:57:22.70 spid20s Unable to open the physical file “G:\MSSQL\Log\MSCRM_MainDB.ldf”. Operating system error 5: “5(Access is denied.)”.
As we can see above, we have issues with file permission. I asked him to check service account in SQL Server Configuration Manager and found that the service user account was not having permission on the G drive. We added the user and restarted the service and issue was resolved. So far, so good. He asked me an interesting question which I was able to answer and this blog is to share that answer.
Whenever a database is restarted, it must go through the recovery process to bring it online. When the state is “RECOVERY PENDING” then it means that recovery process could not start at all. The most common reasons would be an incorrect file path, files missing or incorrect permissions on the files/folders.
On the other hand, state SUSPECT means recovery process was started, but it failed in any of three phases (analysis, redo, undo). This would mark database as Suspect. I think this was changed in SQL 2005.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
