Troubleshooting potential file and storage issues on Managed Instance

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

Azure SQL Managed Instance enables you to choose the amount of storage that you would need. You would need to monitor your database to see do 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 the 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 some table that you don't need.
    • Check 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 some query or process that causes peaks in the storage usage - for example REBUILD INDEX that fills the load, 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 tier 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 the error while you are trying to add file on general purpose instance or restore the database with a large number of files check 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 the error when you are trying to add files on general Purpose instance and you have not reached the 280 files per instance limit, check 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 the errors related to TEMPDB space usage on General Purpose tier, maybe you are reaching the TEMPDB limit that is 24GB/core. Try to remove some objects from TEMPDB  or optimize queries that are using a lot of memory and causes TEMPDB spills, and if this is not possible, you would need to upgrade your Managed Instance to 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 the error while trying to add and remove file with the message like "this operation cannot be performed", check is there some conflicting operation 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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.