Survey: data files and filegroups per database (code to run)

This survey is closed – please don’t spend your time sending any more data – thanks

In this survey I’d like to see what the distribution of the number of data files and filegroups per database is for your servers.

Please run the following code (with example output):

    COUNT (*) AS [DataFiles],
    COUNT (DISTINCT data_space_id) AS [Filegroups],
    SUM (size) AS [Size]
FROM sys.master_files
WHERE [type_desc] = N'ROWS' -- filter out log files/data_space_id 0
    AND [database_id] > 4  -- filter out system databases
    AND [FILE_ID] != 65537 -- filter out FILESTREAM
GROUP BY [database_id];
DataFiles   Filegroups  Size
----------- ----------- -----------
1           1           288
1           1           288
3           2           25600

And send me the results for as many servers as you want. You can either email me plaintext or a spreadsheet or append the results as a comment to this post. Please do not add any more info to the results (like server name, version etc.) as it’s not relevant for this survey and adds a bunch of time to the results processing.

Note that the script will only work on 2005 onward.

I’ll editorialize the results in a week or two.


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.