SQL Server 2019 – Is Accelerated Database Recovery Available?

recoverysql serversql-server-2019transaction-log

SQL Server 2019 CTP introduced a new feature – accelerated database recovery. But then something strange started to happen:

If we look at doc page about this feature, we would see no mention of
SQL Server 2019 – there’s Azure DB only.
If we look at sys.databases
description, we would see no mention of
is_accelerated_database_recovery_on column, while in fact, it exists
Same thing about accelerated_database_recovery database option.
However, feature matrix shows ADR available for Standard and Enterprise SQL Server editions.

On the one hand, enabling ADR could benefit with faster transaction rollback and lesser startup time.

On the other hand, feature seems undocumented. Therefore, it’s behavior is unpredictable. If something goes wrong with transaction log, I could literally lose some data.

So, does anybody knows: can I rely on ADR using SQL Server 2019? Is it postponed due to some instability or it is just a glitch in documentation?

Best Answer

The documentation page link in your question is specific to Azure SQL Database. Note Azure in the url:

https://docs.microsoft.com/en-US/azure/sql-database/sql-database-accelerated-database-recovery.

The Accelerated database recovery page in the general documentation shows ADR is included in SQL 2019 and Azure SQL Database.

Related Solutions

Sql-server – SQL Server shows database in recovery

_{Community wiki answer originally left as an edit to the question by its author:}

The problem was that the database property AutoClose was set on.

The solution was to set AutoClose to false.

SQL Server – Database Recovery Complete?

There are couple of things to note here. The database will only be accessible by query seamlessly and would allow connection if all three phases of recovery has completed. The three phases are analysis, redo and Undo(sometimes if DB is in consistent state after redo an UNDO phase would not be required). In enterprise edition the database would come online after second phase (this is called Fast recovery which was introduced from SS 2005 onwards) and you would see database as online but still internally third phase, the UNDO, phase is going on and would be accessible completely only after third phase completes. What I mean by completely is For fast recovery, a query may bump into one of the locks being held to allow fast recovery – in which case it will have to wait for that lock to be dropped as UNDO progresses.Please read more about Fast recovery

In standard edition database would only come online after all three phases have completed.

You can read more about three phases of recovery

You can check status of database using sys.databases view

select state from sys.databases

In errorlog if it is written for particular database that Recovery is complete then you can be assured that database is online and can be accessed

EDIT

EDIT: How do i tell when a database is ONLINE but still in the UNDO recovery stage?

Well easiest method is reading errorlog it would have information like

Recovery of database 'TestDB' (5) is 3% complete (approximately 1294 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:47.76 spid18s     Recovery of database 'TestDB' (5) is 3% complete (approximately 1281 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:47.78 spid18s     Recovery of database 'TestDB' (5) is 3% complete (approximately 1281 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2015-03-05 10:07:47.99 spid18s     CHECKDB for database 'TestDB' finished without errors on 2013-08-13 21:40:44.543 (local time). This is an informational message only; no user action is required.
~DATABASE ONLINE

If you look at logs you could see information related to what phases completed

I would say you should not worry about when database would come online because in any case it will always follow three phases of recovery (when undergoing crash recovery) you should focus on why Unexpectedly SQL server restarted. This should be avoided at all cost. Make sure SQL Server is patched to latest service pack

Best Answer

Related Solutions

Sql-server – SQL Server shows database in recovery

SQL Server – Database Recovery Complete?

Related Question