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 ?
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 ?
Hi @sakuraime ,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
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.
of coz what I mention here is regarding the transaction log usage on rollback comparing database with and without ADR enabled.
which means both databases
-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.
but the different only is ADR enable on one of it , the transaction log growth larger during rollback.


and some seconds later

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.
ALTER DATABASE A SET RECOVERY SIMPLE <<< can you set to FULL mode and try again ?
ALTER DATABASE A SET RECOVERY SIMPLE <<< can you set to FULL mode and try again ?
THAT'S WHY I ASKED YOU TO PROVIDE A FULL REPRO, SO WE DON'T HAVE TO PLAY THESE GUESSING GAMES!
If you try my script yourself, you will find that the already after the UPDATE, the log size is is 794624 KB after the UPDATE with ADR, and 860160 KB with ADR. That is, the ROLLBACK has nothing to do with it.
I've not dug into this in detail, but I would guess SQL Server also needs to log the data pages for the PVS.
14 people are following this question.