Finding out how much space is used by filestream files
At the Manchester user group the other night a question from the floor was “How do you determine the space used by the Filestream files” so I thought I would look into it.
I found the same question posted on Stack Overflow and the following was given as a means to get an approximate answer.
SELECT SUM(DATALENGTH(filestreamcolumn)) FROM
A follow up reply also points you to the sys.database_files system view to retrieve the size of the filestream filegroup, but it also points out that it does not necessarily give you an accurate value of the total space used and it doesn't tell you the amount of space available on the volume. This seems like an ideal situation to use Powershell as it involves the file system and SQL Server, so I then looked into ways of doing it that way
I found this article on Allen White’s blog which does most of the SMO I need to carry out the task, but to get to get the directory size I would need to do something like this script which I found on TechNet.
After putting it all together, I came up with this script. Most of my additional code is in the Filestream-Path procedure which uses the WMI to get the volume information, but before that I split the filegroup path into it’s ancestry using the split operator. This will then go through each ancestor from the oldest to the youngest and if volume information is returned then it must be a disk mount point. The last volume found will be the most recent ancestor.
When running this script make sure you will get errors if you don’t have sufficient permissions on the folders.