部署磁碟空間監控後收到告警，但缺乏歷史成長資料無法判斷成因。本文實作一套自動化收集 sys.master_files 資訊、計算環比/同比成長率、支援趨勢分析的監控系統。
DiskCapacityHistory：記錄每日各資料庫檔案大小、成長類型、環比/同比sp_diskcapacity_cal：每日排程執行，寫入快照並計算成長率USE msdb;
GO
IF OBJECT_ID('dbo.DiskCapacityHistory', 'U') IS NOT NULL
DROP TABLE dbo.DiskCapacityHistory;
GO
CREATE TABLE dbo.DiskCapacityHistory
(
Date_CD INT NOT NULL, -- YYYYMMDD
DataBaseID INT NOT NULL,
FileID INT NOT NULL,
DataBaseName sysname NOT NULL,
LogicalName VARCHAR(32) NOT NULL,
FileTypeDesc NVARCHAR(60) NOT NULL, -- ROWS / LOG
PhysicalName NVARCHAR(260) NOT NULL,
StateDesc NVARCHAR(60) NOT NULL,
MaxSize NVARCHAR(32) NOT NULL, -- 不允許增長 / 自動增長 / 具體 GB
GrowthType NVARCHAR(8) NOT NULL, -- 10% / 64MB
IsReadOnly INT NOT NULL,
IsPercentGrowth SMALLINT NOT NULL,
Size FLOAT NOT NULL, -- GB
Growth_MOM_RAT FLOAT NULL, -- 環比成長率 (%)
Growth_YOY_RAT FLOAT NULL, -- 同比成長率 (%)
CONSTRAINT PK_DiskCapacityHistory PRIMARY KEY (Date_CD, DataBaseID, FileID)
);
GO
CREATE OR ALTER PROCEDURE dbo.sp_diskcapacity_cal
AS
BEGIN
SET NOCOUNT ON;
-- 1. 插入今日快照
INSERT INTO dbo.DiskCapacityHistory
(Date_CD, DataBaseID, FileID, DataBaseName, LogicalName, FileTypeDesc,
PhysicalName, StateDesc, MaxSize, GrowthType, IsReadOnly, IsPercentGrowth, Size)
SELECT
CAST(REPLACE(CONVERT(CHAR(10), GETDATE(), 120), '-', '') AS INT), -- Date_CD
database_id, file_id, DB_NAME(database_id), name, type_desc,
physical_name, state_desc,
CASE
WHEN max_size = 0 THEN N'不允許增長'
WHEN max_size = -1 THEN N'自動增長'
ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'GB'
END,
CASE
WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(10)) + '%'
ELSE CAST(growth AS VARCHAR(10)) + 'MB'
END,
is_read_only, is_percent_growth,
CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(10,4)) -- Size (GB)
FROM sys.master_files;
-- 2. 計算環比 (MoM) 與同比 (YoY) 成長率
-- 環比：今日 vs 上月同一天；同比：今日 vs 去年同一天
;WITH Today AS (
SELECT * FROM dbo.DiskCapacityHistory
WHERE Date_CD = CAST(REPLACE(CONVERT(CHAR(10), GETDATE(), 120), '-', '') AS INT)
),
PrevMonth AS (
SELECT * FROM dbo.DiskCapacityHistory
WHERE Date_CD = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(MONTH, -1, GETDATE()), 120), '-', '') AS INT)
),
PrevYear AS (
SELECT * FROM dbo.DiskCapacityHistory
WHERE Date_CD = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(YEAR, -1, GETDATE()), 120), '-', '') AS INT)
)
MERGE INTO dbo.DiskCapacityHistory AS Target
USING (
SELECT
T.Date_CD, T.DataBaseID, T.FileID,
CASE WHEN P.Size IS NULL OR P.Size = 0 THEN 0
ELSE (T.Size - P.Size) / P.Size END AS Growth_MOM_RAT,
CASE WHEN Y.Size IS NULL OR Y.Size = 0 THEN 0
ELSE (T.Size - Y.Size) / Y.Size END AS Growth_YOY_RAT
FROM Today T
LEFT JOIN PrevMonth P
ON T.DataBaseID = P.DataBaseID AND T.FileID = P.FileID
LEFT JOIN PrevYear Y
ON T.DataBaseID = Y.DataBaseID AND T.FileID = Y.FileID
) AS Source
ON Target.Date_CD = Source.Date_CD
AND Target.DataBaseID = Source.DataBaseID
AND Target.FileID = Source.FileID
WHEN MATCHED THEN UPDATE SET
Target.Growth_MOM_RAT = Source.Growth_MOM_RAT,
Target.Growth_YOY_RAT = Source.Growth_YOY_RAT;
END;
GO
-- 使用暫存表 + UPDATE 替代 MERGE
CREATE TABLE #GrowthCalc (
Date_CD INT, DataBaseID INT, FileID INT,
Growth_MOM_RAT FLOAT, Growth_YOY_RAT FLOAT
);
INSERT INTO #GrowthCalc
SELECT T.Date_CD, T.DataBaseID, T.FileID,
CASE WHEN P.Size IS NULL OR P.Size = 0 THEN 0 ELSE (T.Size - P.Size) / P.Size END,
CASE WHEN Y.Size IS NULL OR Y.Size = 0 THEN 0 ELSE (T.Size - Y.Size) / Y.Size END
FROM dbo.DiskCapacityHistory T
LEFT JOIN dbo.DiskCapacityHistory P
ON T.DataBaseID = P.DataBaseID AND T.FileID = P.FileID
AND T.Date_CD = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(MONTH, 1, CAST(CAST(P.Date_CD AS CHAR(8)) AS DATE))), '-', '') AS INT)
LEFT JOIN dbo.DiskCapacityHistory Y
ON T.DataBaseID = Y.DataBaseID AND T.FileID = Y.FileID
AND T.Date_CD = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(YEAR, 1, CAST(CAST(Y.Date_CD AS CHAR(8)) AS DATE))), '-', '') AS INT)
WHERE T.Date_CD = CAST(REPLACE(CONVERT(CHAR(10), GETDATE(), 120), '-', '') AS INT);
UPDATE DCH
SET Growth_MOM_RAT = G.Growth_MOM_RAT, Growth_YOY_RAT = G.Growth_YOY_RAT
FROM dbo.DiskCapacityHistory DCH
JOIN #GrowthCalc G
ON DCH.Date_CD = G.Date_CD AND DCH.DataBaseID = G.DataBaseID AND DCH.FileID = G.FileID;
DROP TABLE #GrowthCalc;
| 操作 | T-SQL (SQL Server) | PL/SQL (Oracle) |
|---|---|---|
| 日期 → 整數 (YYYYMMDD) | CAST(REPLACE(CONVERT(CHAR(10), GETDATE(), 120), '-', '') AS INT) | TO_CHAR(SYSDATE, 'YYYYMMDD') |
| 整數 → 日期 | CAST(CAST(Date_CD AS CHAR(8)) AS DATE) | TO_DATE(Date_CD, 'YYYYMMDD') |
結論：Oracle 語法更簡潔直觀，T-SQL 需多層嵌套轉換。
Oracle 支援 LAG() OVER (PARTITION BY ... ORDER BY ...)，單一查詢即可完成所有分組的環比同比，無需自我連接：
-- Oracle 風格 (單查詢搞定)
MERGE INTO TargetTable T
USING (
SELECT
Date_CD, DataBaseID, FileID,
(Size - LAG(Size) OVER (PARTITION BY DataBaseID, FileID ORDER BY Date_CD))
/ NULLIF(LAG(Size) OVER (PARTITION BY DataBaseID, FileID ORDER BY Date_CD), 0) AS Growth_MOM_RAT,
(Size - LAG(Size, 12) OVER (PARTITION BY DataBaseID, FileID ORDER BY Date_CD))
/ NULLIF(LAG(Size, 12) OVER (PARTITION BY DataBaseID, FileID ORDER BY Date_CD), 0) AS Growth_YOY_RAT
FROM DiskCapacityHistory
) S
ON (T.Date_CD = S.Date_CD AND T.DataBaseID = S.DataBaseID AND T.FileID = S.FileID)
WHEN MATCHED THEN UPDATE SET
T.Growth_MOM_RAT = S.Growth_MOM_RAT,
T.Growth_YOY_RAT = S.Growth_YOY_RAT;
SQL Server 2012+ 也支援 LAG/LEAD，強烈建議升級後改寫，大幅簡化邏輯並提升效能。
DiskCapacityHistory 繪製成長趨勢圖
