This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
SQL Server 2019 Database stuck In Recovery state after restart
Question
SQL Server was restarted by mistake, when it came online, database came back in "In Recovery" mode.
Check from error it says "Recovery of database ‘DB1’ (5) is 8% complete (approximately 27146 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
It says it will 8 hours to bring this 2tb database online.
Any quick way to fix this, as we didnt had anything open in LOG files, so even if they r ignored, its no impact.
We want to bring this database ONLINE quickly
asked 2020-09-23 by user3657339
Answer
There’s not really anything you can do. Your database is going through crash recovery, and it needs to process the transaction log. I’ve written about this exact scenario more in depth here.
If SQL Server hadn’t completed a CHECKPOINT recently, there would be database changes that were committed, but had only been made in memory & in the transaction log, but not in the data files. These changes need to be redone from the log file so that the changes aren’t lost. This is what your server is doing right now. I describe the phases in more detail in the article I linked to earlier.
If your transaction log is unnecessarily very large, this can cause SQL Server to do checkpoints less frequently. A too large log file, or too many VLFs can both result in a longer recovery time after a crash.
For a planned restart you can minimize/eliminate the delay by performing a manual CHECKPOINT; before shutting down and by allowing the SQL Server service to gracefully stop. Rebooting the server without first stopping the service can result in a forced termination of the sqlservr.exe process, leading to recovery like you’re seeing on startup.
To get your database back online, just be patient. Don’t restart, don’t try to get rid of your log file. Just be patient. That estimated recovery time is notoriously incorrect, so hope that it’s online sooner, rather than later.
answered 2020-09-23 by Andy Mallon