計算 FileStream 數據列長度匹配已用 FS 驅動器已用空間

請在下面給我建議：

我正在計算具有以下數據類型（包括視圖和使用者模式表）的 LOB 對象的總大小。

select * from information_schema.columns where data_type in 
   ('TEXT', 'NTEXT','IMAGE' ,'XML', 'VARBINARY')
   or 
   (data_type = 'VARCHAR' and character_maximum_length = -1)
   OR
   (data_type = 'NVARCHAR' and character_maximum_length = -1)

一旦我得到以下查詢的輸出，我將使用 DATALENGTH 函式來獲取每列的大小。

SELECT SUM(DATALENGTH('ColumnName')) / 1048576.0 AS ColumnName  FROM DatabaseName.schema.Tablename

注意： 輸出將是兆字節，因為我正在使用**/ 1048576.0**

綜上所述，用於儲存文件流數據的總驅動器大小為1 TB 。但是使用 DATALENGTH 函式計算每個 LOB 列的長度後，它與 Drive 上目前使用的空間不匹配。

例如 ：

目前驅動器大小為 1 TB，其中 951 GB 是可用空間 0.99 TB。這意味著文件流數據類型列使用 50 GB。但是，當我檢查 T-SQL 上方的所有列時，我無法獲得與已用空間 (50 GB) 匹配的確切大小。甚至沒有接近的尺寸。它們顯示出非常少的使用量。

除了上面提到的數據類型之外，還有什麼可以使用驅動器？

如何使用 DATALENGTH 函式計算列的大小，該函式將與 File Stream Drive 中的已用空間相匹配。

文件流卷上每個文件的空間以文件系統分配單元（塊大小）為單位分配。當實際數據大小不是分配單元大小的精確倍數時，分配的空間將向上舍入到下一個分配單元大小的倍數。

下面的 PowerShell 命令將顯示伺服器上每個卷的分配單元大小：

Get-WmiObject `
   -Query "SELECT Name, Label, Blocksize FROM Win32_Volume WHERE FileSystem='NTFS'" `
   | Select-Object Name, Label, Blocksize;

使用上述命令返回的 blocksize 值，您可以使用如下查詢確定為每個文件流文件分配的實際空間：

DECLARE @AllocationUnitSize decimal(10,0) = 65536;
SELECT
     CEILING(DATALENGTH(file_stream)/@AllocationUnitSize)*@AllocationUnitSize AS AllocatedSpaceBytes
   , DATALENGTH(file_stream) AS UsedSpaceBytes
FROM dbo.YourFileStreamTable;

對於小文件和大分配單元大小，未使用的分配空間量將非常重要。例如，考慮 1K 的實際文件大小將需要 64K 與該 AU 大小，每個文件浪費 63K 的儲存空間。

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