Share via

Azure MI: Release unused space for Files in quicker way (around 1TB)

Kishore A 1 Reputation point
2021-05-24T07:12:52.927+00:00

There is a huge table around 1TB of space, distributed across multiple file groups under one file group.

Now we have decommission the table as that is no longer needed ( dropped the table using SQL DROP STATEMENT). As the table was distributed under multiple file group, now file group shows free space. Storage Utilization for MI is still not showing the available space, as the space is not released yet from files.

I am trying to use DBCC SHRINKFILE to release unused space, DBCC SHRINKFILE or SHRINK through GUI for individual files is taking too long.

is there a better and quicker way of releasing the unused space, to make it available for Azure MI instance storage ?

Azure SQL Database
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kishore A 1 Reputation point
    2021-05-24T07:17:40.613+00:00

    @Oury Ba-MSFT Please let me know, if you have any suggestions on the abouve

  2. Oury Ba-MSFT 18,021 Reputation points Microsoft Employee
    2021-05-24T23:41:20.88+00:00

    @Kishore A Thanks for posting your question. SHRINKFILE to release unused space is not a fast process, it will likely take hours to shrink these files.
    We would recommend doing one file at a time and script out the entire process and run in TSQL (SSMS).
    You will want to run this one file at a time else your transaction log could fill up and cause a rollback. (depending on how that is configured)

    But most importantly, once you do shrink the MDF/NDF files, your database (all tables and indexes) will likely be 100% fragmented.
    So just keep in mind you will likely want to do a defrag after the shrink process.

    is there a better and quicker way of releasing the unused space, to make it available for Azure MI instance storage ?
    Not sure if there is a quicker way to freed up unused space for SQL MI.

    Regards,
    Oury