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

  • Ok, I got it. https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/ . https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ . 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 (). 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(, notruncate) followed by dbcc shrinkdatabase(, truncateonly): no results.
    • Maybe I have to shrink the single files, so I executed dbcc shrinkfile(): still the same.
    • Maybe I have to shrink the files to a specific value, so I executed `dbcc shrinkfile(, 😞 again, no luck.

    This query

        [BaseData] as (
                [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]
        [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:

        [BaseData] as (
                [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 https://www.brentozar.com/archive/2020/07/what-if-you-really-do-need-to-shrink-a-database/ 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 (, '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?

  • 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, https://dadepretto.com/troubleshooting/2022/02/08/dbcc-shrinkdatabase-is-not-working-oh-my-azure.html .

Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2