SQL Server – ALTER DATABASE MODIFY FILEGROUP AUTOGROW_ALL_FILES

sys.filegroups contains a column is_autogrow_all_files that indicates if the setting is already turned on for the filegroup in question.

This should show a list of alter database statements you can run:

DECLARE @cmd NVARCHAR(2000);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC 
FOR
SELECT '
select ''ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILEGROUP '' + QUOTENAME(ds.name) + '' AUTOGROW_ALL_FILES;''
from ' + QUOTENAME(d.name) + '.sys.data_spaces ds
    INNER JOIN ' + QUOTENAME(d.name) + '.sys.filegroups fg ON ds.data_space_id = fg.data_space_id
WHERE fg.is_autogrow_all_files = 0;
'
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT (@cmd);
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The code will display a set of statements you can run for each database on the instance, to modify each file group.

And example of the statement generated is:

select 'ALTER DATABASE [msdb] MODIFY FILEGROUP ' + QUOTENAME(ds.name) + ' AUTOGROW_ALL_FILES;'
from [msdb].sys.data_spaces ds
    INNER JOIN [msdb].sys.filegroups fg ON ds.data_space_id = fg.data_space_id
WHERE fg.is_autogrow_all_files = 0;

You can't work on the partition <=> files relationship. Partitions are just affected to a Filegroup. However, splitting your Filegroup into different files will help with performance. The numbers I always use is to have the number of partitions per Filegroup set to the MAXDOP value. I got this looking at how SQL Server split the tempdb into different files and I did notice some improvement copying this behavior.

Regarding partitions, what you should do is split them on filegroups so that the ones that are no longer updated never move. We typically have a Hot filegorup in our DWH, and a Cold one. The cold one is Read Only and we only change that status on the day we move data from Hot to cold. You could do this without having to create one filegroup per partition. That just makes it harder to manage.