Azure SQL Database
An Azure relational database service.
6,341 questions
Instead of using DBCC SHRINKDATABASE please try to use DBCC SHRINKFILE to release allocated space..
May I know how to release unused space in the database in Azure SQL Managed Instance?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 94%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERB%3C/text%3E%3C/svg%3E)
Ramesh Babu
21
Reputation points
May I know how to release unused space from the database in Azure SQL Managed instance ? My Database showing 100GB size but used space is only 1GB, I have tried to shrink the database but unused space is still not released, Can some one help me on this ?
Azure SQL Database
{count} votes
-
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator2023-08-09T16:52:52.7633333+00:00 @Ramesh Babu Thank you for reaching out.
My understanding is that you are trying to release unused space from your Azure SQL Database Managed Instance. As @Alberto Morillo mentioned below you can use DBBC SHRINKEFILE. After the shrink operation is complete, execute the
sp_spaceusedcommand to verify that the unused space has been released.Please don't forget to mark as accept answer if the reply was helpful.
Regards,
Oury
Sign in to comment
3 answers
Sort by: Most helpful
-
Alberto Morillo 34,676 Reputation points MVP Volunteer Moderator2023-08-09T16:19:55.9833333+00:00 -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(153.6, 66%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EW%3C/text%3E%3C/svg%3E)
way0utwest 81 Reputation points MVP2023-08-09T16:50:02.67+00:00 It would be good to know what you tried. If you clicked Shrink Database in SSMS, this uses SHRINKDATABASe, as Alberto noted.
You can instead click the files option, shown here:
Once you've done that, you can fill out the dialog, as per your specs.
If you want to use DBCC SHRINKFILE as a code option, connect to your MI and you can see examples here: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16&source=recommendations#examples
-
Javier Villegas 905 Reputation points MVP2023-08-09T16:59:03.4133333+00:00 Hello,
you can shrink the data and log file in MI same as on SQL Server but note that IO depends on file size. as big is the data file more IOPS/Throughput will be available so if you reduce the size from 100 GB to 1 or 2 GB you will notice performance impact
see "File IO characteristics in General Purpose tier" under below link