Azure SQL Managed Instance enables you to choose the amount of storage that you need. You would need to monitor your database to see if you have enough storage to operate and take some corrective actions if you are reaching the storage limit. In addition, there are some constraints that Managed Instance introduces and technical/architectural characteristics that might affect your workload.
If you are getting errors related to storage and file management, here are some actions that might help you to troubleshoot and solve the issues:
- If you are getting storage limit errors like "Could not allocate a new page for database ... because of insufficient disk space in filegroup ...', try to release some space by using DBCC SHRINK command or try to TRUNCATE a table that you don't need.
- Check if are you reaching the storage limit by querying master.sys.server_resource_stats view:
select top 1 start_time, storage_space_used_mb, reserved_storage_mb,
[storage usage %] = 100 * (storage_space_used_mb/reserved_storage_mb)
from master.sys.server_resource_stats
order by start_time desc
- If you are not reaching the current instance storage limit, check have you reached the limit in the past because maybe some files are shrunk in the meantime. The following query returns per-hour storage usage on your instance.
select [time] = DATEADD(hour, DATEDIFF(hour, 0, start_time), 0),
[storage usage %] = 100 * max(storage_space_used_mb/reserved_storage_mb)
from master.sys.server_resource_stats
group by DATEADD(hour, DATEDIFF(hour, 0, start_time), 0)
order by DATEADD(hour, DATEDIFF(hour, 0, start_time), 0) desc
Try to identify a query or process that causes peaks in the storage usage - for example REBUILD INDEX that fills the log file, a big ETL process that loads a huge amount of data, etc.
- If you cannot shrink database or remove some data and your storage limit is below 4TB for Business Critical tier and 8TB for General Purpose tier, you would need to add more storage to the instance. Note that this process is not instant, and it might take up to few hours.
- If you have already reached the 4TB max storage limit of the Business Critical instance try to change tiers to General Purpose where you have up to 8TB storage.
- If you have already reached the max storage limit of the instance (4TB for business Critical and 8TB for General Purpose tiers) and you have no other options, you might consider moving some databases to another instance by using cross instance point-in-time restore feature to free some space on the current instance.
- If you are getting an error while you are trying to add file on general purpose instance or restore the database with a large number of files check if are you reaching the limit of 280 files per instance by querying master.sys.master_files. Try to empty and delete some unnecessary files in other databases.
- If you are getting an error when you are trying to add files on a General Purpose instance and you have not reached the 280 files per instance limit, check if are you reaching the internal 35TB limit due to file fragmentation. In some specific configuration of file sizes your max file number limit might be lower than 280 per instance due to internal fragmentation and you would need to remove some files.
- If you are getting errors related to TEMPDB space usage on General Purpose tier, you might be reaching the TEMPDB limit that is 24GB per core. Try to remove some objects from TEMPDB or optimize queries that are using a lot of memory and causes TEMPDB spills. If this is not possible, you would need to upgrade your Managed Instance to a higher tier that has more cores and TEMPDB space. The following query returns TEMPDB data size:
select tempdb_size_gb = sum(size*8./1024/1024/1024) from master.sys.master_files
where db_name(database_id) = 'tempdb' and type_desc = 'ROWS'
- If you are getting an error while trying to add and remove files similar to "this operation cannot be performed", check for conflicting operations such as upgrading service tier, backup, or TDE encryption. In most of the cases you can retry the operation later.
- If you are making some changes in the TEMPDB such as adding or removing the data files, changing the size that either return errors or they are reset after some time, beware that you cannot modify the TEMPDB. TEMPDB is periodically rebuilt as an empty database and your changes are either blocked or reset. If you need some objects in TEMPDB, you can create SQL Agent job that will re-create them either periodically or when the service starts.
