0

I currently run into an issue, that a SQL Server Database has the state 'recovery pending' every morning. No one has started a database recovery, the server itself decides to do it.

The recovery process gets stuck and the SQL Server stops working, as its memory limit is reached.

Temporarily, I can resolve it by restarting the MSSQLSERVER process and then taking the database offline and back online. However, as this seems to be happening every day now, this is an unacceptable workaround.

I am eager to know:

  • What are the possible reasons, that a SQL Server automatically starts such an automated restore process
  • Are there any Logs / Events I can look for, to find the reason.
  • How can I solve it :)

Any help or hint will be highly appreciated.

Update: Database was in recovery pending, not in restore pending . The SSMS is installed in german language and didn't show me the english status.

Improve this question
2
  • @Learning_DBAdmin: It is a recovery process not a restore process
    – Felix
    Commented Aug 19, 2020 at 10:35
  • My mistake, removed the comment.
    – Learning_DBAdmin
    Commented Aug 19, 2020 at 11:50

1 Answer 1

Reset to default
1

I have a feeling that what you really have is "recovery pending" and not "restore pending". If you look at the documentation for sys.databases, the state column, you will see that there isn't a state called "restore pending". So, I will assume it is indeed "recovery pending" below. If it is anything else, please let us know, show is what it look like in SSMS Object Explorer as well as the state column from sys.databases.

Recovery pending means that SQL server couldn't do recovery at startup of the database. Note that start of the database can happen outside startup of SQL Server. Having the autoclose database option set to true is one such case.

Anyhow, whenever recovery isn't doable, the database goes into recovery pending. But SQL server will document why, in the errorlog file. So your task is to hunt down these error messages and act on them. My guess is that any of the database files are used by some other process (anti-virus, snapshot thingie, etc). But at a later time, the files are accessible to SQL Server.

Improve this answer
5
  • Hi Tibor, yes, that's what happens when someone installs SQL Server in german -.- So from the logs, I'd say that there is a memory issue first, and then the recovery fails. However, it looks like a recovery is a more frequent task than I thought.
    – Felix
    Commented Aug 19, 2020 at 10:17
  • For comlete information: A Link to the Microsoft SQL-Documentation about the Server-Errorlog learn.microsoft.com/de-de/sql/relational-databases/performance/…
    – Felix
    Commented Aug 19, 2020 at 10:27
  • I disabled the auto_close feature and I'll see whether that did the trick
    – Felix
    Commented Aug 19, 2020 at 10:33
  • Wow, you had autoclose turned on? Do you know why? This is something re don't want - for several reasons, except for very very rare cases. Hehe, I get your point about the language translation challenge. :-) Memory issue, you say? Do you have memory optimized tables? I would investigate why it goes to recovery pending (regardless if autoclose to off "solves" the situation).
    – Tibor Karaszi
    Commented Aug 19, 2020 at 11:26
  • It's turned on per default in SQL Server Express installations. Since I turned off the auto_close feature, I had no issues with recovery or memory usage. Further investigations won't be paid, so I guess I'll leave it there. Although I'm so eager to know what exactly was happening.
    – Felix
    Commented Aug 24, 2020 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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