SQL Server 2005 監控資料庫檔案大小：利用 sp_msforeachdb 批次擷取 sys.database_files

需求背景

DBA 需定期掌握資料庫檔案大小趨勢，以便規劃空間、設定告警、安排高負載時段。SQL Server 2005 提供 sys.database_files 系統檢視，包含檔案大小、成長模式、唯讀狀態等資訊。

解決方案：兩步驟自動化收集

步驟 1：建立儲存表 DatabaseFiles

IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
    SELECT TOP 0 * INTO DatabaseFiles FROM sys.database_files;
    ALTER TABLE DatabaseFiles ADD CreationDate DATETIME DEFAULT (GETDATE());
END

結構同 sys.database_files，額外加 CreationDate 記錄收集時間。

步驟 2：跨資料庫批次插入

EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files';

sp_msforeachdb 會遍歷執行個體所有資料庫，以 [?] 佔位資料庫名稱，將各資料庫檔案資訊一次寫入 DatabaseFiles，免寫游標。

查詢收集結果

SELECT * FROM DatabaseFiles;

結果包含：資料庫名、檔案邏輯名、實體路徑、大小（8KB 頁數）、最大大小、成長增量/百分比、是否唯讀、狀態、收集時間等。

注意事項

• 範例使用 SELECT * 為簡化示範，生產環境建議明確列出欄位名稱。
• sp_msforeachdb 為非文件化程序，未來版本可能移除，長期方案可考慮 PowerShell 或 SMO。