SQL Database Stuck in Recovery Mode

Kmcnet 706 Reputation points
2023-11-30T02:12:44.33+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,311 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 43,161 Reputation points
    2023-11-30T07:44:31.15+00:00

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

    Viewing the SQL Server Error Log

    0 comments No comments

  2. ZoeHui-MSFT 35,226 Reputation points
    2023-11-30T07:53:27.7033333+00:00

    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.


  3. Rahul Randive 9,176 Reputation points Microsoft Employee
    2023-11-30T13:38:05.2033333+00:00

    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!

    0 comments No comments