Take the 2-minute tour ×
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.

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
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.