question

sakuraime avatar image
0 Votes"
sakuraime asked sakuraime commented

SQL Server 2019 ADR questions

the database in sql server 2019 enabled ADR , when I update a column like size in 10GB , will the original mdf for that database grow 10GB due to version store ?

And also for rollback, the modified extent are overwrite from that in the version store , how long will it takes ? Or just some back end metadata pointing or switch over??

and for the insert statement , will it also create version store ??

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered sakuraime commented

Hi @sakuraime,

will the original mdf for that database grow 10GB due to version store

No. The extra versioning information leads the mdf file growing, but the size will less than 10 GB. There is a new asynchronous cleaner process, which iterates through all relevant databases (similar to ghost cleanup) and deallocates unneeded page versions. It runs every minute.

Suggest you to read the blog Accelerated Database Recovery in SQL Server 2019 and How Does Accelerated Database Recovery Work?


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I did some test , it's around same size of the data which I modified . suppose you have a table with 10 columns , you update one 1 columns (for all rows), it will save all the rows to the version stores..

what about the insert ? and also for rollback, the modified extent are overwrite from that in the version store , how long will it takes ? Or just some back end metadata pointing or switch over??

0 Votes 0 ·