Tuesday, March 1, 2022

How-to discover database files space configurations on Azure SQL Managed Instance

Writing about database is impossible without writing a lot about file management. Previously in the article How-to find out reserved and available Disk Space on SQL MI, we have already have explained some of the concepts and shared some of the scripts for discovery of the reserved and available disk space. In this post we shall focus on details - how every single individual file need to be taken care of and how you can use the T-SQL script to determine which files are needed to be taken care of.

 

If you are interested in other posts on how-to discover different aspects of SQL MI - please visit the  http://aka.ms/sqlmi-howto, which serves as a placeholder for the series.

 

Database file management inside SQL Server is an essential task and has been such since the very first version of the product. This is one of the key tasks attributed to the responsibilities of DBA (Database Administrator) and on Azure SQL Managed Instance this task does fade away but becomes even more important. Managing space on Azure SQL Managed Instance involves reserving the space and attributing it to the databases allocated on this Instance. There are important details that should not escape the attention of those who are using SQL Managed Instance and this blog will dive into the specific details of how to discover current database file configuration for updating and monitoring it.

 

Both currently available service tiers are applicable to the management requirements described in this post. Notice that the InMemory OLTP technology and its file management is outside of the scope of this article.

Available Space

Besides the currently reserved space by each of the database files (data or log), an essential aspect to be aware of is the actual space within database file that is occupied by information. The growth of information within data or log file will eventually attempt to autogrow the respective data or log file and if there is no more either reserved space or defined with maximum file size, then the operation will fail, and the current transaction will fail as well.

 

Autogrowth


The automated growth of the database files is configured with the help of the ALTER DATABASE […] MODIFY FILE command for each of the data and log files and can be set either in percentage, fixed number of data or it can be set equal to zero (0) meaning that the autogrowth is not allowed for this specific file.

 

Notice that the customer can always increase the size of the database files manually, by defining it with the help of the same ALTER DATABASE […] MODIFY FILE command, instead of waiting for the automated process.

If there is a need to decrease the size of the database file and there are conditions for this operation to take place (such as free space in the data files, for example), this operation can be executed with the help of DBCC SHRINKFILE(...) command.

 

FileMaxSize


Besides the limit of the reserved space, each of the data and log files has a setting for the maximum size, defining the maximum space available for the data or transactions. This setting like the previous ones can be changed with the help if the same ALTER DATABASE […] MODIFY FILE command by specifying the MAXSIZE parameter and for more details, please consult the official documentation.

 

By default, on Azure SQL Managed Instance the maximum size of all current database files is set to the current reserved space for the whole instance. This is a thoughtful setting, but if your database size is growing fast and the customer is constantly increasing the reserved space for Azure SQL Managed Instance – without updating the maximum file size, it will remain with the original value and if that value is reached the transaction will get rolled back and no information update will be possible.

 

A very important current limitation for the General Purpose service tier dictates that the maximum size for a data file is set to 8 TB, while the maximum size for a single log file (we do not allow multiple ones currently) for the SQL Managed Instance is still 2 TB.

 

Putting it all together


For extracting most of this information, we can use the sys.master_files DMV which contains the information on the database actual file size, maximum size, autogrowth details (percentage or fixed number), besides the basics such as the database file name, type and the respective database reference through database_id.
We shall also need the total reserved and total used storage information from the sys.server_resource_stats DMV, the detailed information on the locally available space, which will be outside of the reserved space for the General Purpose service tier which we extract from the sys.dm_os_volume_stats DMV.

 

The most complicated part is the free space within each of the database file, which can be obtained from the sys.database_files DMV, but it has to be done within the context of each of the respective databases. For that purpose we shall need to write a cursor for extracting this information from each database individually and store it in the temporary table #DBFileFreeSpace defined for this purpose.

 

Joining the above information with the above mentioned DMVs we can build a single query that will extract and present the required information on database file management within Azure SQL Managed Instance:

 

 

DROP TABLE IF EXISTS #DBFileFreeSpace;

CREATE TABLE #DBFileFreeSpace
(
    dbName NVARCHAR(128), 
    dbFileName NVARCHAR(128), 
    dbFileDesc NVARCHAR(128),
    CurrentSizeGB DECIMAL(9,3), 
    FreeSpaceGB DECIMAL(9,3)
);
    

DECLARE @sqlCommand NVARCHAR(2000);
DECLARE @dbName NVARCHAR(2000);
DECLARE @sqlFileSpaceCommand NVARCHAR(2000) = 
					'SELECT DB_NAME() AS dbName, name AS dbFileName, 
							type_desc as dbFileDesc,
							size/128.0 / 1024 AS CurrentSizeGB,  
							size/128.0 / 1024 - (CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 / 1024)  AS FreeSpaceGB
					FROM sys.database_files
					WHERE type IN (0,1);'

DECLARE cdb CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
	FOR 
		SELECT QUOTENAME(name) FROM sys.databases ORDER BY name;

OPEN cdb;

FETCH NEXT FROM cdb 
	INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sqlCommand = CONCAT('USE ', @dbName, ';', @sqlFileSpaceCommand);
	
	INSERT INTO #DBFileFreeSpace(dbName, dbFileName, dbFileDesc, CurrentSizeGB, FreeSpaceGB)
		EXEC sp_executesql @sqlCommand;
            
    FETCH NEXT FROM cdb 
		INTO @dbName;
END

CLOSE cdb;
DEALLOCATE cdb;



SELECT DB_NAME(database_id) as DatabaseName, 
	   name as FileName,
	   type_desc as FileType,
	   CAST(size * 8. / 1024 / 1024  AS DECIMAL(9,3)) as FileSizeGB, 
	   fs.FreeSpaceGB,
	   CASE WHEN max_size = -1 THEN -1 ELSE CAST(max_size * 8. / 1024 / 1024 AS decimal(9,0)) END as FileMaxSizeGB,
	   CASE WHEN mf.is_percent_growth = 1 THEN growth ELSE 0 END as PercentGrowth,
	   CASE WHEN growth = 0 OR mf.is_percent_growth = 1 THEN NULL ELSE CAST(growth * 8. / 1024 / 1024  AS decimal(9,3)) END as FileGrowthGB,
	   CASE WHEN mf.database_id != 2 AND st.ServiceTier = 'GeneralPurpose' 
			THEN st.ReservedStorageGB - st.UsedStorageGB
			ELSE localSpace.LocallyAvailableGB 
	   END as SpaceAvailableGB
	FROM master.sys.master_files mf
	LEFT JOIN #DBFileFreeSpace fs
		ON DB_NAME(mf.database_id) = fs.dbName AND mf.name = fs.dbFileName
	CROSS APPLY( SELECT TOP 1 sku as ServiceTier,
							CAST( reserved_storage_mb / 1024. as DECIMAL(9) ) as ReservedStorageGB, 
							CAST( storage_space_used_mb / 1024. as DECIMAL(9) ) as UsedStorageGB
						FROM [sys].[server_resource_stats] ORDER BY end_time DESC ) as st
	CROSS APPLY( SELECT vs.volume_mount_point as VolumeMountPoint,
						CAST(MIN(available_bytes / 1024. / 1024 / 1024 ) AS NUMERIC(9)) as LocallyAvailableGB
					FROM sys.master_files AS f
						CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs
					WHERE UPPER(vs.volume_mount_point) like 'C:\%' 
					GROUP BY vs.volume_mount_point ) as localSpace
	WHERE type_desc != 'FILESTREAM'
	ORDER BY DB_NAME(database_id), type_desc;

 

 

On the picture below you can see a partial output of the query above for my test SQL Managed Instance with 4 CPU vCores on General Purpose service tier with 1 TB of the reserved space.

NikoNeugebauer_0-1646134871970.png

You can observe several interesting aspects, including he maximum size of some of the system databases, such as [master] and [msdb], which is set to 256 GB for the data file. Also notice that with 12 [tempdb] data files, the individual maximum size for each of them is set to 8 GB and the overall 96 GB of the local SSD storage on the General Purpose service tier are coming from the 4 CPU vCores multiplied times 24 GB per CPU vCore.

 

My database [db1] has around 1 GB of the currently occupied space on the file system, while [db2] has 257 GB for both data and log files, with no percentage growth defined, but with default auto-growth set to just 16 MB, which is something to be corrected. On the last column you can see that from 1 TB of the reserved space, I can still use 495 GB for attributing to my databases.

 

This output will give you a great overview of what is configured for your databases and from this point on you can take important decisions of for example, such increasing the size of your database file sizes or adding an additional data file or maybe even considering shrinking them

Posted at https://sl.advdat.com/3C4SZpfhttps://sl.advdat.com/3C4SZpf