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):
SELECT 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]; GO
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.