Calculating the FileStream Data Columns Length matching used FS Drive used space

Question

Kindly advice me on below :

I am process of calculating the total size of LOB objects with below data types (includes Views and user schema tables) .

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)

Once I get the output of below query , I am using DATALENGTH function to get the size of each column .

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

NOTE : The output will be Megabytes as I am using / 1048576.0

After all the above , my total drive size meant for storing File Stream Data is 1 TB . But post calculating the length of each LOB Column with DATALENGTH function , it does not match up to the current used space on Drive .

For example :

Current Drive size is 1 TB out of which 951 GB is free space 0.99 TB . which means 50 GB is used by File Stream data type columns . But when I check all columns from above T-SQL I could not get exact size matching used space (50 GB) . Not even close size . they show very minimal usage .

What else could be using the drive apart from the data types mentioned above ?

How to calculate the size of columns using DATALENGTH function which will match up to used space in File Stream Drive ..

Answer 1

Space for each file on the file stream volume is allocated in units of the file system allocation unit (blocksize). When the actual data size is not an exact multiple of the allocation unit size, allocated space is rounded up to the next allocation unit size multiple.

The PowerShell command below will show allocation unit size for each volume on the server:

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

Using the returned blocksize value from the above command, you can determine the actual space allocated for each file stream file with a query like:

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

The amount of unused allocated space will be significant for small files and a large allocation unit size. For example, consider that an actual file size of 1K will require 64K with that AU size, wasting 63K of storage per file.