Force Storage Reclamation on Azure SQL Database

Question

Our Azure SQL Server Database has grown too large so we're trying to reduce the costs of service by bringing down the allocated space. We've already bulk-deleted data that we no longer need and freed up plenty of available space, as well as looped through shrinking all the database files. However, we still have 22TB of space allocated when we are currently using only 14TB (with the plan to free up even more space). We are looking for support to force storage reclamation of this free space so that we can bring down the cost. From my current understanding, this might happen automatically if there is no activity on the database for a sustained period, but we use it daily and we were hoping to avoid down-time or having to create a temporary database to switch to until the original shrinks.

Answer 1

You can shrink a database file with DBCC SHRINKFILE. Shrink the data and log file separately. I recommend that you set a target size when you shrink the file.

Don't shrink it too much - you still need some space to grow. Also, shrinking introduces fragmentation, which you can cure by rebuilding the index. But if your shrank it too much it will grow again.

Beware that you if have LOB data, the shirnk can be very sloooooow.