How to shrink 10 TB database in Azure Managed Instance

Question

How to shrink 10 TB database in Azure Managed Instance.

is it possible?

Answer 1

Hi Vijay Kumar

Thanks for reaching out to Microsoft Q&A.

Note: Don't waste time shrinking data files if the regular application workload will cause the files to grow to the same allocated size again. File growth events can negatively impact application performance.

In Azure SQL Database, to shrink files you can use either DBCC SHRINKDATABASE or DBCC SHRINKFILE commands:

• DBCC SHRINKDATABASE shrinks all data and log files in a database using a single command. The command shrinks one data file at a time, which can take a long time for larger databases. It also shrinks the log file, which is usually unnecessary because Azure SQL Database shrinks log files automatically as needed.
• DBCC SHRINKFILE command supports more advanced scenarios:
  • It can target individual files as needed, rather than shrinking all files in the database.
  • Each DBCC SHRINKFILE command can run in parallel with other DBCC SHRINKFILE commands to shrink multiple files at the same time and reduce the total time of shrink, at the expense of higher resource usage and a higher chance of blocking user queries, if they are executing during shrink.
    • Shrinking multiple data files concurrently lets you complete the shrink operation faster. If you use concurrent data file shrink, you might observe transient blocking of one shrink request by another.
  • If the tail of the file does not contain data, it can reduce allocated file size faster by specifying the TRUNCATEONLY argument. This does not require data movement within the file.
• For more information about these shrink commands, see DBCC SHRINKDATABASE and DBCC SHRINKFILE.

Reference : https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage?view=azuresql-db

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.