查詢以報告磁碟空間分配和已用空間

我們總共為一個應用程序使用了 6 個數據庫，我們只能在所有 6 個自動增長數據庫之間共享 4TB 的空間（通過 SAN 儲存）。

我想為單個數據庫編寫一個查詢（報告），在 SQL Server Management Studio 的“任務”>“收縮”>“數據庫”選項下指示“目前分配的空間”和“可用的可用空間”屬性。

然後我想將這些數字轉換為 TB 並對每個數據庫進行總計，以粗略估計我們還剩下多少空間。可以通過 T-SQL 查詢訪問這些欄位嗎？如果是這樣，查詢會是什麼樣子？

以下是 Management Studio 用於填充這些數字的查詢：

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df 
  WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
SUM(a.total_pages) AS [SpaceUsed],
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df 
  WHERE df.type in (1, 3)) AS [LogSize]
FROM
sys.partitions p join sys.allocation_units a 
 on p.partition_id = a.container_id 
left join sys.internal_tables it 
 on p.object_id = it.object_id

您需要像 Management Studio 一樣在此處執行數學運算以獲得相同的數字。此外，左連接sys.internal_tables似乎充其量是多餘的。因此，調整該查詢以匹配您的理想輸出：

SELECT
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
  FROM sys.database_files AS df 
  WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
  FROM sys.database_files AS df 
  WHERE df.type in (1, 3)) AS [LogSize]
FROM sys.partitions p join sys.allocation_units a 
 on p.partition_id = a.container_id;

通過此更新，假設您的數據庫在此期間沒有更改，它應該產生：

753475.94    744030.07    2900.00

做一些簡單的數學運算，然後只分離出你想要的三個數字：

;WITH t(s) AS
(
 SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
  FROM sys.database_files
  WHERE [type] % 2 = 0
), 
d(s) AS
(
 SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
  FROM sys.partitions AS p
  INNER JOIN sys.allocation_units AS a 
  ON p.[partition_id] = a.container_id
)
SELECT 
 Allocated_Space = t.s, 
 Available_Space = t.s - d.s,
 [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
FROM t CROSS APPLY d;

引用自：https://dba.stackexchange.com/questions/29543