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 bydbcc 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?
