Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a sql FILESTREAM enabled table, and are wondering what the fastest way is to determine the size grouped by a common criteria, as well as the sum of all objects.

To date, we have been using queries like select ClientID, sum(datalength(FilestreamColumn)) from dbo.tblFilestream group by ClientID; however, this takes a very long time to complete on a large dataset, and I imagine it is fairly hostile to the buffer cache. I can perform a directory listing, parse the entries in Excel, and get the results back faster than SQL does. However, that's because the dir command isn't reading the contents into memory.

We could add a new column to store this info and maintain it each time we perform an operation, but we'd prefer not to. That being said, we may have no other choice.

Regarding the 'all objects' part of the question - this question indicates that sys.database_files can be used, but it is always 0 for us, even in SQL 2008 R2 SP2.

share|improve this question

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.