I have an Azure Sql Server database (in the cloud, not on a VM)(Not a managed instance) which currently has 270 gigs of space allocated to it.

However, the actual space used is 27% (81 gigs).

I would like to reclaim that space, as on Azure, they charge you by the amount of allocated space. The large amount of unused space is due to dropping a varbinary(Max) column. The column was up to 40 megs of data for each row.

I have checked the docs, and it says to run:

DBCC SHRINKDATABASE ([JGWeiss_Prod_V2])

or

DBCC CLEANTABLE ([JGWeiss_Prod_V2],'dbo.Attachments', 0)

I executed the first one and let it run for 12 hours, and then the second one for another 12 hours, but it results in zero space being reclaimed. I ended up cancelling them, not letting them run to completion.

I have also tried the "TruncateOnly" option with seemly no effect.

Am I doing something wrong? Or do I just need to wait it out, like let one or the other run for several days?

I understand the concept of not doing such commands on a regular basis, but I would like to reduce the Azure charges.

The other option I have read about are doing a bakpak file, and then restoring the entire database and then dropping the original.

Suggestions appreciated.

Update DBCC CLEANTABLE finally ran to completion, but I the allocated space still shows the same.

DatabaseDataSpaceAllocatedInMB  DatabaseDataSpaceAllocatedUnusedInMB
270941.312500                   188090.187500

I then ran DBCC SHRINKDATABASE ([JGWeiss_Prod_V2])

And it completed in a few seconds.

However, still no change in the amount of allocated space.

Is there something else I need to do?