ALTER DATABASE MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES

补充 Max 和 Kenneth 的答案..

我建议您应用SQL Server 2016 CU1，因为存在一个已知的AUTOGROW_ALL_FILES属性错误-

数据库或服务器重新启动后，该属性不会保留。sys.filegroups 视图仍然显示属性的相应列（is_autogrow_all_files 和 is_read_only）设置为 1。但是，它实际上并不有效。

另外，需要注意的是，在SQL Server 2016 中，您不需要 TF 1117，因为is_autogrow_all_files它相当于 TF1117

sys.filegroups包含一列is_autogrow_all_files，指示是否已为相关文件组打开设置。

这应该显示您可以运行的 alter database 语句列表：

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;

该代码将显示一组您可以为实例上的每个数据库运行的语句，以修改每个文件组。

生成的语句示例是：

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;

SSMS 17.8 引入了对此功能的 UI 支持。从SSMS 17.8 现在可用：

在此版本的 SQL Server Management Studio 中，我们为AUTOGROW_ALL_FILES数据库文件组属性引入了 UI 和脚本支持。

此属性在 SQL Server 2016 中引入以替换跟踪标志 1117，但它只能通过 T-SQL 脚本设置。现在您可以通过 Database Properties -> Filegroups 页面中的复选框设置属性

^{图片来源：https ://cloudblogs.microsoft.com/uploads/prod/2018/06/image-1-sql-server.png}

您还可以使用“脚本”按钮编写更改脚本：

USE [AdventureWorks2016_EXT] 

GO

declare @autogrow bit 

SELECT @autogrow=convert(bit, is_autogrow_all_files) 
  FROM sys.filegroups WHERE name=N'PRIMARY' 

if(@autogrow=0) 

ALTER DATABASE [AdventureWorks2016_EXT] 
  MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES 

GO

该设置针对每个文件组。您可以查看 sys.filegroups。

SELECT is_autogrow_all_files, * FROM sys.filegroups

sys.filegroups博尔：https ://msdn.microsoft.com/en-us/library/ms187782.aspx

这是我对数据库中所有文件的均匀文件增长问题的解决方案：

SET NOCOUNT ON 
DECLARE @db_name sysname, @sql NVARCHAR(max)='', @Mainsql NVARCHAR(max)='', @rowCount int

SELECT @Mainsql = @@VERSION
--SELECT @Mainsql 
--Microsoft SQL Server 2008 R2 (SP3) - 10.50.6529.0 (X64)   Mar 19 2015 13:19:13   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 
--Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) 

IF @Mainsql NOT LIKE '%SQL Server 2016%' BEGIN
    SELECT 'This script does not work on servers prior to SQL Server 2016. Please check before continuing'
    SELECT @Mainsql
END

Begin TRY
    DROP TABLE #h
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #h (i INT IDENTITY(1,1), sqltxt NVARCHAR(max))
DELETE FROM #h

DECLARE dbcursor CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT name FROM sys.databases 
    WHERE is_read_only=0 AND state_desc='ONLINE' 
    AND source_database_id is NULL AND name NOT IN ('master', 'model', 'msdb')
    ORDER BY name

OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR (' %s' , 10, 1, @db_name) WITH NOWAIT
    SET @rowCount=0
    SELECT @Mainsql =''
    SELECT @Mainsql += '
    SELECT @rowCount=count(*)
    FROM [' + @db_name + '].sys.filegroups F 
    WHERE type <> ''FD''  /* no FILESTREAM_DATA_FILEGROUP */
    AND type <> ''FX'' /* no MEMORY_OPTIMIZED_DATA_FILEGROUP */
    AND is_autogrow_all_files=0
    '
    BEGIN TRY
        EXEC sys.sp_executesql @Mainsql, N'@rowCount int output', @rowCount OUTPUT
    END TRY
    BEGIN CATCH
        RAISERROR ('Unable to access %s' , 10, 1, @db_name) WITH NOWAIT
        RAISERROR ('SQL: %s' , 10, 1, @Mainsql) WITH NOWAIT
    END CATCH

    --SELECT @Mainsql AS max, @rowCount rowcnt

    IF @rowCount <> 0 BEGIN
        SELECT @sql += 'INSERT INTO #h ( sqltxt ) SELECT ''PRINT'''''' + ''[' + @db_name + ']''''''
        ' 
        SELECT @sql += 'INSERT INTO #h ( sqltxt ) SELECT ''USE '' + ''[' + @db_name + ']''
        ' 
        IF @db_name <>'tempdb' BEGIN 
            SELECT @sql += 'INSERT INTO #h ( sqltxt )
            SELECT ''ALTER DATABASE [' + @db_name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;''
            '
        END 
        SELECT @sql += 'INSERT INTO #h ( sqltxt )
        SELECT ''ALTER DATABASE [' + @db_name + '] MODIFY FILEGROUP ['' + fg.name + ''] AUTOGROW_ALL_FILES''
        FROM [' + @db_name + '].sys.filegroups FG 
        WHERE FG.type <> ''FD''  /* no FILESTREAM_DATA_FILEGROUP */
        AND FG.type <> ''FX'' /* no MEMORY_OPTIMIZED_DATA_FILEGROUP */
        AND FG.is_autogrow_all_files=0
        '

        IF @db_name <>'tempdb' BEGIN 
            SELECT @sql += 'INSERT INTO #h ( sqltxt )
            SELECT ''ALTER DATABASE [' + @db_name + '] SET MULTI_USER;''
            '
        END 
    END 
    --SELECT @sql 
    FETCH NEXT FROM dbcursor INTO @db_name
END

SELECT @sql
EXEC sp_executesql @sql

CLOSE dbcursor
DEALLOCATE dbcursor

SELECT @sql=''
SELECT @sql+=sqltxt + '
' FROM #h H 
ORDER BY i
SELECT @sql
--EXEC sp_executesql @sql

它仍然有问题；如果文件组中没有文件，我会收到一条错误消息。

我希望这会有所帮助。有时在别人使用时执行不会成功。您可以再次运行它或添加脚本以将数据库设置为单一模式。

-- Run the results of this query, It will create queries for all databases in the server.

DECLARE @command varchar(1000) 
SELECT @command = 'use [?]; SELECT ''ALTER DATABASE ? MODIFY FILEGROUP ['' + b.groupname + ''] AUTOGROW_ALL_FILES;'' 
FROM dbo.sysfiles a (NOLOCK)
JOIN dbo.sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
GROUP BY b.groupname'

EXEC sp_MSforeachdb @command