Using Accelerated Database Recovery to Keep Azure SQL Database Logs Under Control

Problem: Long‑running transactions bloating the transaction log

In Azure SQL Database, a single long‑running UPDATE or DELETE can keep the logical log truncation point stuck, causing the transaction log file to grow until it fills the allocated storage. This not only risks hitting storage limits but also slows down database recovery because the entire log must be replayed after a restart.

Thesis: Accelerated Database Recovery (ADR) decouples log truncation from active transactions, giving fast recovery and bounded log growth

ADR maintains a version store inside the database and a persistent log buffer. When a transaction updates a row, the previous version is copied to the version store, allowing the engine to undo changes quickly. The log truncation point can advance past the last active transaction, so the log can be reused even while the transaction is still open. Recovery then only needs to replay the persistent log buffer and apply the version store, which typically takes seconds instead of minutes.

Enabling ADR in Azure SQL Database

ADR is available in the General Purpose and Business Critical service tiers. It is not supported in the Basic tier or on older hardware generations; attempting to enable it on an unsupported tier returns an error.

To turn ADR on, run the following statement as a user with the ALTER ANY DATABASE permission (e.g., a server‑level admin or a user granted CONTROL on the database):

ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = ON;

Replace [YourDatabaseName] with the actual database name. The change takes effect immediately; no database restart is required.

Worked example: Verifying ADR and observing log reuse

1. Check the current state of ADR:

   SELECT name, is_accelerated_database_recovery_on
   FROM sys.databases
   WHERE name = 'YourDatabaseName';
   

   The column is_accelerated_database_recovery_on should return 1.

2. Start a long‑running transaction that updates many rows (run in a separate session):

   BEGIN TRAN;
   UPDATE LargeTable SET SomeColumn = SomeColumn + 1;
   -- Do NOT commit or rollback yet
   

   Keep this session open.

3. Force a checkpoint to flush the log:

   CHECKPOINT;
   

4. Examine the log reuse wait description:

   SELECT log_reuse_wait_desc
   FROM sys.databases
   WHERE name = 'YourDatabaseName';
   

   With ADR enabled, the value should not be 'ACTIVE_TRANSACTION' while the long‑running transaction is still open. Instead you may see 'NOTHING' or another wait type, indicating the log can be reused.

5. Check the version‑store size (approximate):

   SELECT version_store_size_kb
   FROM sys.dm_tran_accelerated_database_recovery;
   

   This column shows the current size of the version store in kilobytes. Monitor it under heavy update workloads to ensure it stays within your storage limits.

6. Commit or roll back the transaction and observe that the log file size does not continue to grow.

Trade‑offs and limitations

• Performance impact: ADR adds modest CPU overhead for version‑store cleanup and slightly more I/O for the persistent version store. In most workloads the reduction in log‑growth contention outweighs this cost, but very write‑intensive workloads may see a small throughput dip.

• Version‑store space consumption: The version store lives in the database files (and uses tempdb for cleanup). Under massive update patterns it can consume several gigabytes. Regularly check sys.dm_tran_accelerated_database_recovery and consider adjusting the database max size or enabling auto‑grow if needed.

• Tier and hardware restrictions: ADR cannot be enabled on the Basic tier or on older hardware generations. If you receive an error, verify your service tier and consider upgrading.

Actionable closing

If your Azure SQL Database frequently encounters log‑growth alerts due to long‑running jobs, enable ADR following the steps above. After enabling, verify the state with sys.databases and sys.dm_tran_accelerated_database_recovery, monitor the version‑store size, and confirm that log_reuse_wait_desc no longer shows 'ACTIVE_TRANSACTION'. Keep an eye on storage usage; if the version store approaches your limits, consider archiving old data or increasing the database max size. With ADR in place, you should see faster recovery times and more predictable log consumption, letting you focus on application logic rather than log‑management fire drills.