SQL Server 2019 ADR : Use more transaction log to rollback

Question

I understand the mdf of the database which enabled ADR will increase due to versioning of the extent. However , I found when rollback, it takes much transaction log space. why ?

Answer 1

Hi @sakuraime ,

There are many reasons, but usually these reasons are of the following several patterns:

-The database recovery model is not a simple model, but no log backups are scheduled. For databases in non-simple mode, the records will be truncated only after the log backup is done.
-There is a transaction on the database that has not been committed for a long time. SQL Server will retain all log records from the point in time when this transaction is started.
-There is a large transaction running on the database. For example, a user is creating/rebuilding indexes, or deleting or inserting large amounts of data with DELETE/INSERT statements.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Sorry, I was not able to reproduce the observation. I ran this script:

USE tempdb
go
DROP DATABASE IF EXISTS A
go
CREATE DATABASE A
ALTER DATABASE A SET RECOVERY SIMPLE
-- ALTER DATABASE A SET ACCELERATED_DATABASE_RECOVERY = ON
go
USE A
go
create table big (col1 varchar(1000))
go

insert into big values (replicate('A',1000))
go 100000

EXEC sp_helpdb A
go
begin tran
update big set col1=replicate('B',1000)

EXEC sp_helpdb A

go
ROLLBACK TRANSACTION
go
EXEC sp_helpdb A
go
WAITFOR DELAY '00:00:10'
go
EXEC sp_helpdb A

And then the same batch with the comment removed. The final file sizes in the first case was 139264 KB for the data file and 401408 KB for the log file. In the second case, the numbers were 270336 KB and 401408 KB.

That is, the data file was bigger (because of the Perisistent Version Store), but the log-file size was the same.