SQL Server 2016 – ALTER DATABASE MODIFY FILEGROUP AUTOGROW_ALL_FILES

sql serversql-server-2016

I would like to change all file groups in my database to AutoGrow_all_Files, but only if it is currently AutoGrow_Single_File, like this:

ALTER DATABASE mydatabase MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES

How can I check this database property?

Best Answer

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;
Related Question