Why dbcc's shrinkdatabase and shrinkfile aren't working?

Question

Ok, I got it. Shrinking your database is wrong. You hate it. But let me explain.

I have a 1TB P1 Azure SQL Database in production with ~50 tables, where ~5 of them are JSON containers. This was the original design, and I quickly realized its limits, so now I'm in the process of offloading the storage of those JSONs to a more appropriate Azure Storage Account.

This process will take time (JSONs are used in different business processes, and I'm migrating one at a time), so I'm currently deleting ranges of rows after a successful migration. Still, I cannot truncate or drop the entire table.

After migrating many business processes, I'm now left with 868.64 GB of allocated space vs. 390.82 GB of used space. Of course, I'd like to lower my storage size to the 400GB tier to reduce costs, but when I try to do it from the Azure Portal, I get the following error message:

The storage size of your database cannot be smaller than the currently allocated size. To reduce the database size, the database first needs to reclaim unused space by running DBCC SHRINKDATABASE (<db_name>). Note that this operation can impact performance while it is running and may take several hours to complete.

Okay, fair enough. So I proceed to execute the command (of course, with the correct database name), and after a couple of hours and successful execution, the situation is precisely the same. No space reclaimed.

After this, I proceeded with the following tentatives:

• Maybe I have to force the reorganization + truncation, so I executed dbcc shrinkdatabase(<db_name>, notruncate) followed by dbcc shrinkdatabase(<db_name>, truncateonly): no results.
• Maybe I have to shrink the single files, so I executed dbcc shrinkfile(<file_name>): still the same.
• Maybe I have to shrink the files to a specific value, so I executed `dbcc shrinkfile(<file_name>, <free_space_on_file_in_mb>): again, no luck.

This query

with
 [BaseData] as (
 select
 [DF].[type_desc] as [Type],
 [DF].[name] as [FileName],
 [DF].[size] / 131072.0 as [TotalSpaceInGB],
 [UP].[size] / 131072.0 as [UsedSpaceInGB],
 ([DF].[size] - [UP].[size]) / 131072.0 as [FreeSpaceInGB],
 [DF].[max_size] as [MaxSize]
 from [sys].[database_files] as [DF]
 cross apply (
 select fileproperty([DF].[name], 'spaceused') as [size]
 ) as [UP]
 )
select
 [BD].[Type] as [Type],
 [BD].[FileName] as [FileName],
 format([BD].[TotalSpaceInGB], N'N2') as [TotalSpaceInGB],
 format([BD].[UsedSpaceInGB], N'N2') as [UsedSpaceInGB],
 format([BD].[FreeSpaceInGB], N'N2') as [FreeSpaceInGB],
 case [BD].[MaxSize]
 when 0 then N'Disabled'
 when -1 then N'Unrestricted'
 else format(([BD].[MaxSize] / 131072.0), N'N2')
 end as [MaxSizeInGB]
from [BaseData] as [BD]
order by [BD].[Type] asc, [BD].[FileName];

always returns the same result:

Type	FileName	TotalSpaceInGB	UsedSpaceInGB	FreeSpaceInGB	MaxSizeInGB
FILESTREAM	XTP	2.03	NULL	NULL	Unrestricted
LOG	log	1.63	0.60	1.03	250.00
ROWS	data_0	509.47	231.58	277.89	512.00
ROWS	dfa_data_3	359.17	159.27	199.91	512.00

Also, this query:

with
 [BaseData] as (
 select
 [TB].[object_id] as [ObjectId],
 max([PT].[rows]) as [RowCount],
 count(distinct [IX].[index_id]) as [IndexCount],
 sum([PS].[used_page_count]) / 131072.0 as [UsedSpaceInGB],
 sum([PS].[reserved_page_count]) / 131072.0 as [ReservedSpaceInGB]
 from [sys].[schemas] as [SC]
 inner join [sys].[tables] as [TB]
 on [SC].[schema_id] = [TB].[schema_id]
 inner join [sys].[indexes] as [IX]
 on [TB].[object_id] = [IX].[object_id]
 inner join [sys].[partitions] as [PT]
 on [TB].[object_id] = [PT].[object_id]
 and [IX].[index_id] = [PT].[index_id]
 left join [sys].[dm_db_index_usage_stats] as [IS]
 on [TB].[object_id] = [IS].[object_id]
 and [IX].[index_id] = [IS].[index_id]
 left join [sys].[dm_db_partition_stats] as [PS]
 on [PT].[partition_id] = [PS].[partition_id]
 and [IX].[index_id] = [PS].[index_id]
 and [TB].[object_id] = [PS].[object_id]
 group by [TB].[object_id]
 )
select top 5
 [BD].[ObjectId] as [ObjectId],
 [BD].[RowCount] as [RowCount],
 [BD].[IndexCount] as [IndexCount],
 format([BD].[UsedSpaceInGB], N'N2') as [UsedSpaceInGB],
 format([BD].[ReservedSpaceInGB], N'N2') as [ReservedSpaceInGB]
from [BaseData] as [BD]
order by [BD].[ReservedSpaceInGB] desc;

clearly shows that tables are not taking up more space than necessary:

ObjectId	RowCount	IndexCount	UsedSpaceInGB	ReservedSpaceInGB
108579475	2892280	1	254.34	254.37
1952114095	834306760	1	79.73	79.74
418204640	20233590	1	23.52	23.53
1599396817	6346104	1	6.63	6.74
1939590048	596471	1	4.75	4.75

I've also made the following considerations:

• I've encountered this post explaining a trick using filegroups, but as far as I know, it's not possible to manage those on Azure SQL Database.
• The problem may be related to the fact that I deleted lots of LOBs. I've found dbcc forceghostcleanup (<db_id>, 'visit_all_pages') command, but I'm hesitant to try it.
• To experiment with dbcc's commands, I've created a clone of the database from a backup. I think this excludes any possible problem related to active transactions holding on to row versions from the accelerated database recovery's version store.
• Ideally, I'd like to avoid as much as possible (use as a last resort) the process of copying data around and dropping the original table or things like so.

Most of the tables in the database are rowstore clustered indexes, except for the 6.63GB one, which is a columnstore clustered index, and seven heaps which all fall under the 40MB mark, both allocated and used. All the tables undergoing the deletion fall in the first category, and also they don't have any nonclustered indexes.

I just tried DBCC UPDATEUSAGE, but it doesn't seem to change anything; sp_spaceused returns the same values.

Do you have any insights?

Answer 1

I've solved the issue by running an ALTER INDEX ALL ON ... REBUILD on all my tables and then performing DBCC SHRINKDATABASE.

I've detailed all the troubleshooting in a post on my blog, here.

(Edit: Fix link)

• Francesco Mantovani: Shouldn't be the other way around, first shrink and then rebuild?
• Davide De Pretto: Yes @FrancescoMantovani, in theory that's the correct order. However that wasn't working in my scenario, which is the whole point of the question. As described in the blog post, I performed a rebuild before and after. So the order becomes: rebuild (to make shrink work) -> shrink (to free up space) -> rebuild (to reduce fragmentation introduced by the shrink).Please note that this was almost a year and a half ago, so maybe newer engine versions solved this bug. I hadn't the chance to try this recently.
• Francesco Mantovani: Testing it right now. Thank you
• Francesco Mantovani: I have this error on SSMS every single time the query finishes snipboard.io/BWLn20.jpg . Sto strange: brentozar.com/archive/2019/03/… . Have you faced this before?
• Davide De Pretto: Hi @FrancescoMantovani, unfortunately I can't help you troubleshoot that problem. My post was regarding the database not shrinking without any error, and I found out the solution was to rebuild indexes before shrinking (and also after, for fragmentation). I've never encountered the errors you've mentioned.

Answer 2

Please try ALTER INDEX ... REORGANIZE WITH (LOB_COMPACTION = ON), followed by another shrink.

As an aside, the shrink documentation article for Azure SQL DB has recently been updated and some of that might be relevant, i.e., shrinking multiple files concurrently and shrinking in steps.

• Davide De Pretto: Thanks for the idea. I've tried it out, but unfortunately, it didn't seem to have any effect.