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

Sid_1805 91 Reputation points
2022-01-31T23:53:34.693+00:00

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

Azure SQL Database
0 comments No comments
{count} vote

Accepted answer
  1. Alberto Morillo 32,796 Reputation points MVP
    2022-02-01T02:41:07.223+00:00

    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.


0 additional answers

Sort by: Most helpful