locked
how to know the size of a filestream storage filegroup? RRS feed

  • Question

  • hi

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

     


    Trinity
    Tuesday, June 14, 2011 12:16 AM

Answers

  • 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

    • Marked as answer by WeiLin Qiao Wednesday, June 22, 2011 1:17 AM
    Wednesday, June 15, 2011 2:56 AM

All replies

  • 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

    • Marked as answer by WeiLin Qiao Wednesday, June 22, 2011 1:17 AM
    Wednesday, June 15, 2011 2:56 AM
  • 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
    Friday, June 17, 2011 9:00 PM
  • 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"
    Sunday, June 19, 2011 11:24 AM