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