How to reclaim space on Sql Server Azure

Question

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.

Answer 1

Now that DBCC CLEANTABLE has run to completion, any freed-up large object pages should have been marked unallocated. That won't reduce the size of the database though, since the allocated pages could have been anywhere in the physical files.

You now have a cheese with holes and need to squish the cheese together so the holes are all at the end, at which point the space-saving knife can be wielded.

DBCC SHRINKDATABASE should achieve that, eventually. Use the WAIT_AT_LOW_PRIORITY option.

Note:

DBCC SHRINKDATABASE operations can be stopped at any point in the process, and any completed work is kept.

It really won't be fast, especially if your Azure database has less than amazing I/O or log throughput. See the related Q & A for the underlying causes and things you might consider:

• Fastest way to shrink LOB data in SQL Server
• What is DBCC SHRINKFILE actually doing?
• Why dbcc's shrinkdatabase and shrinkfile aren't working?
• Reclaiming space from half filled huge FGs having Partitioned and LOB Data in it

Reclaiming space from large object data can be tricky. Follow the analysis steps and methods outlined in the links above for the best chance of winning. Be prepared for frustration.

---

Strangely, even though there are no running DBCC commands, the database continues to shrink in terms of the actual space used.

Once the process is complete, you might still not see full space recovery on Azure SQL Database until any aborted transactions are completely cleaned up (Accelerated Database Recovery cannot be disabled in that version) and the persistent version store (PVS) shrinks.

The documentation contains the following query to see the status:

SELECT
 db_name(pvss.database_id) AS DBName,
 pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
 100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_pct_of_database_size,
 df.total_db_size_kb/1024./1024 AS total_db_size_gb,
 pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
 pvss.current_aborted_transaction_count,
 pvss.aborted_version_cleaner_start_time,
 pvss.aborted_version_cleaner_end_time,
 dt.database_transaction_begin_time AS oldest_transaction_begin_time,
 asdt.session_id AS active_transaction_session_id,
 asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
 pvss.pvs_off_row_page_skipped_low_water_mark,
 pvss.pvs_off_row_page_skipped_min_useful_xts,
 pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid -- SQL Server 2022 only
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (SELECT SUM(size*8.) AS total_db_size_kb FROM sys.database_files WHERE [state] = 0 and [type] = 0 ) AS df 
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

The cleanup runs asynchronously on a background thread. You may speed it up using:

EXEC sys.sp_persistent_version_cleanup [database name];

That command runs synchronously and will not return until complete.

See the Troubleshooting documenation page for more.