SQL Server 資料庫大小分佈查詢腳本

查詢 SQL Server 資料庫與磁碟空間使用情況

這個 T-SQL 腳本可幫助 DBA 快速掌握伺服器上所有資料庫的大小分佈，以及各磁碟機的剩餘空間。透過暫存表搭配未公開的系統預存程序 sp_MSforeachdb 與擴展預存程序 xp_fixeddrives，即可一次撈取所有資料庫檔案大小與對應磁碟剩餘容量。

核心邏輯

1. 建立 #DBsize 暫存表，儲存資料庫名稱、檔案大小（MB）、邏輯檔名、實體路徑。
2. 建立 #drives 暫存表，儲存各磁碟機代號與可用空間（MB）。
3. 用 sp_MSforeachdb 迴圈跑遍所有資料庫，將 sysfiles 系統檢視表的資料寫入 #DBsize。
4. 用 xp_fixeddrives 取得磁碟剩餘空間寫入 #drives。
5. 以檔案路徑首字元（磁碟代號）關聯兩張暫存表，彙總每顆磁碟的資料庫數量、總佔用空間、剩餘空間；並列出每個資料庫檔案的詳細大小與所在磁碟剩餘空間。

完整腳本

SET NOCOUNT ON;

CREATE TABLE #DBsize (
    [DatabaseName] [nvarchar](75) NOT NULL,
    [Size] [decimal] NOT NULL,
    [Name] [nvarchar](75) NOT NULL,
    [Filename] [nvarchar](300) NOT NULL
);

CREATE TABLE #drives (
    [Drive] [char](5) NOT NULL,
    [MBFree] [decimal] NOT NULL
);

INSERT INTO #DBsize
EXEC sp_MSforeachdb 
'SELECT ''?'' AS DatabaseName,
        CASE WHEN [?]..sysfiles.size * 8 / 1024 = 0 THEN 1 ELSE [?]..sysfiles.size * 8 / 1024 END AS Size,
        [?]..sysfiles.name,
        [?]..sysfiles.filename
 FROM [?]..sysfiles';

INSERT INTO #drives
EXEC xp_fixeddrives;

-- 依磁碟機彙總
SELECT @@SERVERNAME AS 伺服器名,
       COUNT(DISTINCT RTRIM(CAST(DatabaseName AS VARCHAR(75)))) AS 資料庫數目,
       Drive AS [使用的磁碟],
       CAST(SUM(Size) AS VARCHAR(10)) AS [總大小(MB)],
       CAST(MBFree AS VARCHAR(10)) AS [剩餘大小(MB)]
FROM #DBsize
INNER JOIN #drives ON LEFT(#DBsize.Filename, 1) = #drives.Drive
GROUP BY Drive, MBFree;

-- 明細到每個資料庫檔案
SELECT RTRIM(CAST(DatabaseName AS VARCHAR(75))) AS [資料庫名],
       Drive AS [盤符],
       Filename AS [檔案路徑],
       CAST(Size AS INT) AS [大小(MB)],
       CAST(MBFree AS VARCHAR(10)) AS [剩餘大小(MB)]
FROM #DBsize
INNER JOIN #drives ON LEFT(#DBsize.Filename, 1) = #drives.Drive
GROUP BY DatabaseName, Drive, MBFree, Filename, CAST(Size AS INT)
ORDER BY [盤符], [剩餘大小(MB)] DESC;

DROP TABLE #DBsize;
DROP TABLE #drives;

使用提醒

• sp_MSforeachdb 為非文件化程序，未來版本可能移除，生產環境建議改用 sys.databases 搭配動態 SQL 實作。
• 腳本以 8KB 頁面換算 MB（size * 8 / 1024），適用於 SQL Server 2005 及以上版本。
• 若資料庫檔案跨多顆磁碟，彙總時會依各檔案所在磁碟分開統計。