SQL 2019 has a new feature name: ADR Accelerated Database Recovery. To enable ADR at database level:
alter database wideworldimportersdw set accelerated_database_recovery = on
Verify ADR is turned on using the following query: is_accelerated_database_recovery_on
select name, compatibility_level, is_accelerated_database_recovery_on from sys.databases
Below Code will illustrate: ADR feature in SQL Server 2019
use WideWorldImportersDW
go
select name, compatibility_level, is_accelerated_database_recovery_on from sys.databases
go
create table howbouttehmcowboys (col1 int, col2 char(100) not null)
go
insert into howbouttehmcowboys values (1, 'Learn SQL 2019')
go 10000
checkpoint
begin transaction
delete from howbouttehmcowboys
go
rollback transaction
go
select top 10 * from sys.fn_dblog(null,null) order by [Current LSN] desc
go
alter database wideworldimportersdw set accelerated_database_recovery = on
select name, compatibility_level, is_accelerated_database_recovery_on from sys.databases
insert into howbouttehmcowboys values (1, 'Learn SQL 2019')
go 10000
checkpoint
go
begin transaction
delete from howbouttehmcowboys
rollback transaction
go
select top 10 * from sys.fn_dblog(null,null) order by [Current LSN] desc
go
Below is the output of the above query.
Looking at the above output we see three important points:
1). sys.fn_dblog before the database ADR is enabled, we see LOP_INSERT_ROWS - These records are compensation log records for the delete. To rollback an insert statement all the associated LOP_INSERT_ROWS needs to be execute. So Rollback takes almost same time as insert.
2). After enabling the ADR and rerunning the same transaction we see LOP_DELETE_ROWS in fn_dblog. This is generated with the actual Delete Statement.LOP_ABORT_XACT is the only statement generated with rollback. ADR is instantaneous, roll back will happen in less than a second.
3). Advantage of ADR is now we not have log growing with long running active logged transactions and we do have to wait for database to recover or transactions to rollback.
--Find Extended events to monitor ADR:
select name, object_type, description from sys.dm_xe_objects where name like '%pvs%'
select name, object_type, description from sys.dm_xe_objects where name like '%ctr%'
--Find SQL Server Wait Types associated with ADR:
select * from sys.dm_os_wait_stats where wait_type like '%pvs%' or wait_type like '%ctr%'
SQL Server ADR Extended Event Wait types.
SQL Server ADR Wait types