2

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?

0

2 Answers 2

2

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)

6
  • Shouldn't be the other way around, first shrink and then rebuild? Commented Jun 2, 2023 at 16:13
  • 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. Commented Jun 5, 2023 at 8:11
  • Testing it right now. Thank you Commented Jun 5, 2023 at 8:35
  • 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? Commented Jun 5, 2023 at 11:09
  • 1
    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. Commented Jun 8, 2023 at 10:04
-1

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.

1
  • Thanks for the idea. I've tried it out, but unfortunately, it didn't seem to have any effect. Commented Feb 8, 2022 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.