How to get size of each database in SQL Managed Instance in Azure ?

Question

How to get size of each database in SQL Managed Instance in Azure ?

Answer 1

The follwing query gives you the size of each database file:

SELECT database_id AS DatabaseID,
       DB_NAME(database_id) AS [Database],
       CAST(size * 8. / 1024 / 1024 AS decimal(12,4)) AS FileSizeGB
FROM sys.master_files
WHERE physical_name LIKE N'https:%'

The following query allows you to keep track of how close the instance is to reach the 35 TB limit.

WITH DatabaseFile AS
(
SELECT database_id AS DatabaseID,
       CAST(size * 8. / 1024 / 1024 AS decimal(12,4)) AS FileSizeGB
FROM sys.master_files
WHERE physical_name LIKE N'https:%'
)
SELECT SUM(FileSizeGB) AS FileSizeGB,
       SUM(
          CASE WHEN FileSizeGB <= 128 THEN 128
               WHEN FileSizeGB > 128 AND FileSizeGB <= 256 THEN 256
               WHEN FileSizeGB > 256 AND FileSizeGB <= 512 THEN 512
               WHEN FileSizeGB > 512 AND FileSizeGB <= 1024 THEN 1024
               WHEN FileSizeGB > 1024 AND FileSizeGB <= 2048 THEN 2048
               WHEN FileSizeGB > 2048 AND FileSizeGB <= 4096 THEN 4096
               ELSE 8192
          END
          )
          AS BlobSizeGB
FROM DatabaseFile;

Hope this helps.