如何按月获取Azure SQL数据库中各表的存储空间大小？

Azure SQL的系统视图（如sys.tables、sys.allocation_units）仅返回实时的表空间数据，没有内置的历史存储记录。要实现按月统计，需要先搭建数据采集机制，再基于历史数据做统计。

1. 创建历史存储表 #

先建一张表用来保存每月采集的表空间数据：

CREATE TABLE TableSpaceHistory (
    StatisticDate DATE PRIMARY KEY,
    TableName NVARCHAR(128),
    SchemaName NVARCHAR(128),
    RowCount BIGINT,
    TotalSpaceKB INT,
    TotalSpaceMB NUMERIC(36,2),
    UsedSpaceKB INT,
    UsedSpaceMB NUMERIC(36,2),
    UnusedSpaceKB INT,
    UnusedSpaceMB NUMERIC(36,2)
);

2. 编写数据采集存储过程 #

把你现有的查询逻辑封装成存储过程，每月执行时将当前表空间数据插入历史表：

CREATE PROCEDURE CollectTableSpaceMonthly
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取当前统计月份的第一天（统一统计时间节点）
    DECLARE @StatDate DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);

    -- 先删除当月已存在的统计数据（避免重复采集）
    DELETE FROM TableSpaceHistory WHERE StatisticDate = @StatDate;

    -- 插入当月表空间数据
    INSERT INTO TableSpaceHistory (
        StatisticDate,
        TableName,
        SchemaName,
        RowCount,
        TotalSpaceKB,
        TotalSpaceMB,
        UsedSpaceKB,
        UsedSpaceMB,
        UnusedSpaceKB,
        UnusedSpaceMB
    )
    SELECT 
        @StatDate,
        t.NAME AS TableName,
        s.Name AS SchemaName,
        p.rows AS RowCount,
        SUM(a.total_pages) * 8 AS TotalSpaceKB, 
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
        SUM(a.used_pages) * 8 AS UsedSpaceKB, 
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE 
        t.NAME NOT LIKE 'dt%' 
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255 
    GROUP BY 
        t.Name, s.Name, p.Rows;
END;

3. 设置定期执行任务 #

• 如果是Azure SQL托管实例/单数据库（有SQL代理）：创建一个SQL代理作业，设置为每月1日执行EXEC CollectTableSpaceMonthly;。
• 如果是无服务器Azure SQL数据库：使用Azure自动化账户创建Runbook，通过PowerShell或TSQL脚本调用存储过程，设置每月触发的计划。

4. 按月查询统计数据 #

历史数据采集完成后，就可以轻松查询每月的表空间变化：

-- 查询所有表的每月存储空间
SELECT 
    StatisticDate AS 统计月份,
    SchemaName,
    TableName,
    TotalSpaceMB AS 总空间(MB),
    UsedSpaceMB AS 使用空间(MB),
    UnusedSpaceMB AS 未使用空间(MB),
    RowCount AS 行数
FROM TableSpaceHistory
ORDER BY StatisticDate DESC, TotalSpaceMB DESC;

-- 按月份汇总所有表的总存储空间
SELECT 
    StatisticDate AS 统计月份,
    SUM(TotalSpaceMB) AS 所有表总空间(MB),
    SUM(UsedSpaceMB) AS 所有表使用空间(MB)
FROM TableSpaceHistory
GROUP BY StatisticDate
ORDER BY StatisticDate DESC;

内容的提问来源于stack exchange，提问作者Learner