What is the best way to change the tier of an Azure SQL Database when allocated space prevents you from doing so?

Question

I've also asked this on Stack Overflow: (https://stackoverflow.com/questions/67060461/what-is-the-best-way-to-change-the-tier-of-an-azure-sql-database-when-allocated)

Our database recently had gotten too full and we needed to remove some data. When we tried to do so, we got an error about the size quota as follows:

   Msg 40544, Level 17, State 12, Line 1  
   The database '<My Database>' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.  

When we looked for a solution, we found this resource (https://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management.aspx), which said that “When total database size on a machine exceeds 90% of total space available on machine, all databases become read-only. Load balancer ensures the situation is resolved by balancing databases across machines.”

At the time, our database was on an S4 tier, which can be up to 1 TB in size. We really wanted to avoid this, but we decided to change the database to be on an P11 tier, which increased the size to 4TB, but increased the cost per month from ~$450 to ~$7000.

We were able to delete the data we needed to, but when we tried to revert the tier back to S4 from P11, we were told that “The storage size of your database cannot be smaller than the currently allocated size.”

Indeed, the allocated space was now at 1.13 TB for some reason

Then, much to my dismay, I found this (https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-database-or-sql-managed-instance-database-used-data/ba-p/2162130)

"The amount of formatted file space made available for storing database data. The amount of space allocated grows automatically, but never decreases after deletes. This behavior ensures that future inserts are faster since space does not need to be reformatted."

We really need to get back down to an S4 tier database. Our worst case plan is that we create a brand new database, migrate our data to that new database, update any references to the old database to use the new database, then delete the old database, but does anyone know of a better way to resolve the issue?

Answer 1

Please try to shrink the database and the log file.

DBCC SHRINKFILE (log, 0)

DBCC SHRINKDATABASE (N'db1')

Adjust the MAX SIZE of the database accordingly also:

ALTER DATABASE DATABASE_NAME
MODIFY (EDITION='BUSINESS', MAXSIZE=750 GB)

After that try to scale down the database.