SQL Database Stuck in Recovery Mode

Question

Hello everyone and thanks for the help in advance. I am running Windows Server 2022 with SQL Server 2019. One of our applications that utilizes SQL Server stopped input, instead providing a timeout error. The server was rebooted, and the database went into recovery mode. Eventually, the database came back online and was perfectly usable. I am not a SQL Server expert, but started going through event logs, but found nothing terribly out of the ordinary. So I am not sure what happened and how to troubleshoot the event so it doesn't happen again. There is an application that uses SQL Notification that was running and appeared to close shortly before the crash, so I am not sure if this application had anything to do with it or how to identify the problem. Any help as to where to start would be appreciated.

Answer 1

Instead Windows EventLog, lookup the SQL Server ErrorLog file(s) for details, see

Viewing the SQL Server Error Log

Answer 2

Hi @Kmcnet,

Possible causes for the databases to stuck in “In Recovery” mode?

• Huge size of transaction log file.
• SQL restarted during a long running transaction.
• Huge number of VLFs (i.e. virtual Log Files).

The very first thing that you should do is checking of ERRORLOG. In Errorlog, we should see the very first message in the database.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @Kmcnet

As recommended, it is necessary to examine the event log and error logs to identify the root cause.

If no relevant details are found, you may need to configure usage and diagnostic data collection for SQL Server in case this issue occurs again

You can configure Group Policy settings on the server to opt in or out of crash dump collection

Please find detailed information in this document - https://learn.microsoft.com/en-us/sql/sql-server/usage-and-diagnostic-data-configuration-for-sql-server?view=sql-server-ver16#set-registry-subkeys-for-crash-dump-collection

Thank you!