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.
| DbId | FileId | CurrentSize | MinimumSize | UsedPages | EstimatedPages |
|---|---|---|---|---|---|
| 11 | 2 | 90624 | 1024 | 90624 | 1024 |
I have also tried DBCC SHRINKDATABASE ([JGWeiss_Prod_V2], TRUNCATEONLY)
However, still no change in the amount of allocated space.
Is there something else I need to do?
Update 2
Strangly, even though there are no running DBCC commands, the database continues to shrink in terms of the actual space used. It is down to 16.95%. It takes many hours to reduce, but something seems to be running that continues to reduce the actual space used. The allocated space however remains the same. So right now, I am just going to wait it out until the used space no longer is reducing.
Update 3 After approximately 4 days, the database whittled its way down to 4 gigs of actual used space (from over 250!) dropping little by little. During this time the allocated space stayed at 270 gigs. But once it got to the end of the shrink, the tail was truncated down to 5 gigs of allocated space. This allowed me to greatly reduce the cost of the database. So, mission accomplished.
