how to know the size of a filestream storage filegroup?

Question

hi

How can I find the size of FILESTREAM data from SQL Server?

 

---

Trinity

Answer 1

Hi Trinity,

You can use the Transact-SQL DATALENGTH() function to determine the size of the BLOB if it is closed:

SELECT 
  DATALENGTH(ColumnName) AS SizeOfData
FROM TableName

Note that the FILESTREAM files are not stored in the SQL Server Database Engine, it is come-consuming to get the length of a BLOB file。For more information, please take a look at FILESTREAM Best Practices.

 

---

Best Regards,
Stephanie Lv

Answer 2

Yes noticed that, but need to calcualte for induvidual column which is huge effort in our case to identify all columns using filestream and script it.

 

 

---

Trinity

Answer 3

Hi Trinity.

For FileStream Data is actually stored out of SQL Server in a folder you specify while creating the database, you if you want to know the size, check the size of that folder

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
  FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
  FILENAME = 'c:\data\filestream1')
LOG ON ( NAME = Archlog1,
  FILENAME = 'c:\data\archlog1.ldf')
GO

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"