Issue
We recently received an end user case where the customer complained of fluctuations in size of an Azure SQL DB.
Error
The cx noticed this on their Azure Portal Monitoring screen, the same is shared below:
Root Cause
We investigated the issue and explained the customer that during the issue timeframe they had a few Long running Sessions in their Azure SQL DB. The Long running sessions caused the Persistent Version Store (PVS) to increase in size as long running open/active transactions might have prevented PVS from being cleaned up & hence its size increased.
The increase in PVS exactly matched with the fluctuations in the DB size both in timeframes and the DB size (in GB). This was hence concluded as the root cause of the size fluctuations observed by the customer. Long Running Sessions--> Increase in PVS Size--> Fluctuations in DB Size
This is also well documented in our public documentation.
Workaround/Fix
Azure SQL Database uses Persistent Version Store (PVS) as part of Accelerated Database Recovery (ADR) to maintain row versions for transactions. While ADR greatly improves recovery and concurrency, its PVS can sometimes lead to unexpected database size fluctuations.
Under normal condition, PVS space usage is minimal and fluctuates modestly. However, if something impedes the cleanup process (Like Long Running Transactions) or generates an extreme volume of versions, PVS can accumulate a large amount of data, leading to a sudden increase in used space.
Conversely, when a backlog of versions is finally cleaned up, the reported “used space” in the database can drop noticeably (though the file size may remain the same until shrunk).
In this case the customer’s issue was self-healed once the long running transactions completed and the cleanup process was able to shrink the PVS after some time and that led to the DB size come back to normal.
As a proactive measure, the customers can monitor the size of the PVS using the script below:
The customers can also monitor the long running sessions on their Azure SQL DB using the script below:
References
Accelerated Database Recovery (ADR) - SQL Server | Microsoft Learn
Monitor and Troubleshoot Accelerated Database Recovery - SQL Server | Microsoft Learn
Microsoft