Check Azure SQL DB Space Used

• Cláudio Silva

  Ten Centuries

  Points: 1006

  More actions

  March 29, 2024 at 12:00 am

  #4390120

  Comments posted to this topic are about the item Check Azure SQL DB Space Used

• Michael Studholme

  Newbie

  Points: 5

  More actions

  April 3, 2024 at 1:59 am

  #4406695

  There's potential for deleted databases to show up as well, a join to sys.databases will prevent that:

  SELECT
      [database_name],
      start_time AS 'LastCollectionTime',
      storage_in_megabytes AS 'CurrentSize(MBs)',
      allocated_storage_in_megabytes AS 'AllocatedStorage(MBs)'
    FROM (
              SELECT
                  ROW_NUMBER() OVER(PARTITION BY [database_name] ORDER BY start_time DESC) AS rn,
                  [database_name],
                  start_time,
                  storage_in_megabytes,
                  allocated_storage_in_megabytes
              FROM sys.resource_stats
          ) rs
  INNER JOIN sys.databases d ON d.name = rs.database_name
  WHERE rn = 1

  (with a performance hit as usual)