SQL Server 效能監控實戰：資料庫與資料表大小每日追蹤與成長分析腳本

資料庫快速增長需持續監控。本文提供一套完整的每日排程腳本，記錄資料庫總大小、各資料表筆數與空間佔用，並自動與昨日比較計算成長量，支援 Linked Server 遠端監控，直接部署於 DBA 維護資料庫即可產出趨勢報表。

核心資料表結構

• TB_DBA_Database_Size：Date_Time, Database_Name, DB_Size(MB), Size_Type
• TB_DBA_MES_Table_Count：Date_Time, Table_Name, Row_Count
• TB_DBA_Database_Table_Size：Date_Time, Name, Rows, Reserved, Data, Index_Size, Unused (KB)

1. 取得資料庫總大小 (MB)

CREATE PROC [dbo].[Proc_DBA_Database_Size] AS
INSERT INTO TB_DBA_Database_Size
SELECT CONVERT(VARCHAR(20), GETDATE(), 120) AS Date_Time,
       'InsiteProdDG' AS [Database Name],
       CAST(SUM(size) * 8 / 1024.0 AS DECIMAL(10,2)) AS [DB Size],
       'MB' AS Size_Type
FROM Srv_MES.InsiteProdDG.dbo.sysfiles;  -- Linked Server 遠端

關鍵：1024.0 強制浮點運算，避免整數除法截斷。

2. 取得各表筆數 (每日全量)

CREATE PROC [dbo].[Proc_DBA_MES_Table_Count] AS
DECLARE @dt VARCHAR(20) = CONVERT(VARCHAR(20), GETDATE(), 120);
INSERT INTO tb_dba_mes_table_count
SELECT @dt, b.name, MAX(a.rows)
FROM Srv_MES.InsiteProdDG.dbo.sysindexes a
JOIN Srv_MES.InsiteProdDG.dbo.sysobjects b ON a.id = b.id
GROUP BY b.name ORDER BY 3 DESC;

3. 取得各表空間大小 (游標逐表呼叫 sp_spaceused)

CREATE PROC [dbo].[Proc_DBA_MES_Table_Size] AS
DECLARE @tb_name VARCHAR(50), @tb_list TABLE (tb_name VARCHAR(50)),
        @tb_size TABLE (name VARCHAR(50), rows VARCHAR(50), Reserved VARCHAR(50),
                        Data VARCHAR(50), Index_Size VARCHAR(50), Unused VARCHAR(50));

DECLARE cur CURSOR FOR
  SELECT Table_Name FROM TB_DBA_MES_Table_Count
  WHERE CONVERT(VARCHAR(10), date_time, 120) = CONVERT(VARCHAR(10), DATEADD(DAY, -1, GETDATE()), 120);

OPEN cur; FETCH NEXT FROM cur INTO @tb_name;
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT @tb_size EXEC Srv_MES.InsiteProdDG.dbo.sp_spaceused @tb_name;
  FETCH NEXT FROM cur INTO @tb_name;
END
CLOSE cur; DEALLOCATE cur;

INSERT INTO TB_DBA_Database_Table_Size
SELECT CONVERT(VARCHAR(20), GETDATE(), 120), * FROM @tb_size;

4. 計算資料庫每日成長量

CREATE PROC [dbo].[Proc_DBA_Database_Size_Grow] AS
SELECT date_time, database_name, db_size, size_type FROM TB_DBA_Database_Size
WHERE database_name = 'InsiteProdDG' AND CONVERT(VARCHAR(10), date_time, 120) = CONVERT(VARCHAR(10), GETDATE(), 120)
UNION ALL
SELECT date_time, database_name, -db_size, size_type FROM TB_DBA_Database_Size
WHERE database_name = 'InsiteProdDG' AND CONVERT(VARCHAR(10), date_time, 120) = CONVERT(VARCHAR(10), DATEADD(DAY, -1, GETDATE()), 120);

5. 計算前 10 大表每日成長量 (含筆數/空間各維度)

CREATE PROC [dbo].[Proc_DBA_MES_Table_Grow] AS
SELECT CONVERT(VARCHAR(20), GETDATE(), 120) AS date_time, name,
       SUM(rows) AS rows, SUM(reserved) AS reserved, SUM(data) AS data,
       SUM(index_size) AS index_size, SUM(unused) AS unused, 'KB' AS Size_Type
FROM (
  -- 今日前10大表
  SELECT TOP 10 name,
    CAST(rows AS INT) rows,
    CAST(LEFT(reserved, LEN(reserved)-2) AS INT) reserved,
    CAST(LEFT(data, LEN(data)-2) AS INT) data,
    CAST(LEFT(index_size, LEN(index_size)-2) AS INT) index_size,
    CAST(LEFT(unused, LEN(unused)-2) AS INT) unused
  FROM TB_DBA_Database_Table_Size
  WHERE CONVERT(VARCHAR(10), date_time, 120) = CONVERT(VARCHAR(10), GETDATE(), 120)
  ORDER BY CAST(rows AS INT) DESC
  UNION ALL
  -- 昨日同樣表 (若存在) 取負值
  SELECT name, -CAST(rows AS INT), -CAST(LEFT(reserved, LEN(reserved)-2) AS INT),
         -CAST(LEFT(data, LEN(data)-2) AS INT), -CAST(LEFT(index_size, LEN(index_size)-2) AS INT),
         -CAST(LEFT(unused, LEN(unused)-2) AS INT)
  FROM TB_DBA_Database_Table_Size
  WHERE CONVERT(VARCHAR(10), date_time, 120) = CONVERT(VARCHAR(10), DATEADD(DAY, -1, GETDATE()), 120)
    AND name IN (SELECT TOP 10 name FROM TB_DBA_Database_Table_Size
                 WHERE CONVERT(VARCHAR(10), date_time, 120) = CONVERT(VARCHAR(10), GETDATE(), 120)
                 ORDER BY CAST(rows AS INT) DESC)
) x GROUP BY name ORDER BY 3 DESC;

部署建議

1. 建立 DBA 專用資料庫存放上述資料表與預存程序。
2. 設定 Linked Server Srv_MES 並啟用 RPC Out (屬性→伺服器選項)，否則遠端 sp_spaceused 會報錯 7411。
3. 啟用分散式交易協調器 (MSDTC) 於雙向伺服器，允許入站/出站、無需驗證，解決「無法啟動分散式交易」錯誤 (7391)。
4. 建立 SQL Agent Job 每日固定時段 (如 02:00) 依序執行 5 支程序。
5. 前端報表直接查詢 *_Grow 程序或歷史表繪製成長趨勢圖。