ADR Does Not Reverse Committed Schema Changes in Azure SQL Database

Does ADR Make a Committed DDL Undoable?

Short answer: No. Once a DDL statement is committed the change is durable. ADR only speeds the undo of uncommitted work and does not affect the persistence of committed changes. Reverting a committed schema change still requires a database‑level point‑in‑time restore (PITR) within the backup retention window.

Is There a Published Threshold for Unsafe In‑Place Rollback?

Microsoft does not publish a universal size, duration or resource limit that declares an uncommitted DDL “unsafe” for in‑place rollback, even when ADR is enabled. The safety of a rollback depends on the workload, the size of the affected objects, the service tier, and the underlying hardware generation.

How to Confirm ADR is Reducing Rollback Blocking on Your Tier

1. Verify ADR is enabled:

   SELECT database_ddl_compatibility_level, accelerated_recovery_state
   FROM sys.database_recovery_status
   WHERE name = DB_NAME();

   State 1 means ADR is active. If it returns 0, enable it with ALTER DATABASE db SET ACCELERATED_RECOVERY = ON; (subject to tier support).

2. Check tier & hardware generation:

   SELECT subscription_id, service_objective, sku_name, is_hardware_generation_2
   FROM sys.database_service_objectives;

   ADR is supported on v12.0+ engines on Generation 2 or newer. If your database is on an older generation, ADR will be ignored.

3. Run a controlled test:
   • Start a long, uncommitted ALTER TABLE that touches a large table (e.g., adding a column with a default).
   • While the transaction is open, monitor sys.dm_tran_locks, sys.dm_tran_version_store, and sys.dm_exec_requests for wait types LCK_M_SCH_M and VERSIONSTORE.
   • Compare the rollback duration and wait statistics with ADR enabled versus disabled (use ALTER DATABASE db SET ACCELERATED_RECOVERY = OFF; for the test).
4. Interpret the data:
   • A progressing rollback shows steady VERSIONSTORE activity and a decreasing number of rows in the version store, with no growth in blocked sessions.
   • Escalating lock contention is evident when LCK_M_SCH_M waits grow, blocked sessions increase, and the version store remains static.

Monitoring Signals to Distinguish the Two Scenarios

Signal	Progressing Rollback	Escalating Contention
sys.dm_tran_version_store rows	Decreasing over time	Stagnant or increasing
Blocked sessions (sys.dm_os_waiting_tasks)	Stable or decreasing	Increasing, often tied to LCK_M_SCH_M
Wait type distribution (sys.dm_exec_requests)	Mostly VERSIONSTORE	High proportion of LCK_M_SCH_M
Rollback duration (estimated via client or sys.dm_exec_requests)	Shorter with ADR on	Longer, often > minutes

Missing Diagnostic Detail

If you do not yet know the exact service tier or hardware generation of your database, that information is critical for determining ADR support and expected behavior. Please provide the output of SELECT service_objective, sku_name, is_hardware_generation_2 FROM sys.database_service_objectives; so the recommendation can be tuned to your environment.