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 
    (data_type = 'VARCHAR' and character_maximum_length = -1)
    (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 ..

1 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;
      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.

  • Hi @Dan , thanks . Kindly correct my understanding . I did use the PS code and yes the AU Block size is 65536 Bytes . Currently I have 8584 rows returned for single column in a File stream table . Like wise I do have 45 columns and tables which are Varbinary , Image , text , xml .... which includes views , user defined tables . Can I calculate "Rows Returned x 65536 = Output / 1024 / 1024 " that outputs me final output for each column in Megabytes ? Likewise I will do for remaining 44 Columns that will take me to approximate size used for File stream Volume which I mentioned in my question . Dec 27, 2017 at 10:12
  • 1
    @DBOn, my answer was only for FILESTREAM columns, Space for LOB columns that are not FILESTREAM are stored in database data pages, which may be in-row or in separate LOB pages depending on the column size. Assuming all values are stored in separate LOB pages, the rough calculation for MB per LOB column would be SUM(CEILING(DATALENGTH(lob_column)/8192.0))/128. Note that views (except for indexed views) are not persisted and therefore require no storage.
    – Dan Guzman
    Dec 27, 2017 at 13:50
  • 1
    @DBOn, FILESTREAM columns are varbinary(MAX) columns with the FILESTREAM property. The other types are stored in the database data files, not as separate files in a FILESTREAM filegroup.
    – Dan Guzman
    Dec 27, 2017 at 14:41
  • 1
    The space usage calculation is only valid for data persisted in tables and indexed vies. Non-indexed views, functions, et.al do require separate storage because the data is stored in the referenced objects, although there may be memory and temporary storage needs during query execution like any other query.
    – Dan Guzman
    Dec 29, 2017 at 11:56
  • 1
    LOB columns that are not FILESTREAM are stored in database data files as specified by the ON filegroup specification. Note that the allocation unit size isn't relevant in that case since it's within the data file(s).
    – Dan Guzman
    Dec 29, 2017 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.