0

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

0
1

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.

0
0

On SQL Server 2019 you could in the future use Accelerated Database Recovery (ADR).

The primary benefits of ADR are:

  • Fast and consistent database recovery

    With ADR, long running transactions do not impact the overall recovery time, enabling fast and consistent database recovery irrespective of the number of active transactions in the system or their sizes.

  • Instantaneous transaction rollback

    With ADR, transaction rollback is instantaneous, irrespective of the time that the transaction has been active or the number of updates that has performed.

  • Aggressive log truncation

    With ADR, the transaction log is aggressively truncated, even in the presence of active long running transactions, which prevents it from growing out of control.

It isn't on by default for on-premises SQL Server, but can be enabled per-database.

ADR is enabled by default in Azure SQL Database and SQL Managed Instance and cannot be disabled.

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