SQL Server enable trace flag 1117 with alter database

env: Windows Server 2016
        SQL Server 2016 SP2

上次在"SQL Server speedup create index in alwayson"透過trace flag 610策是建立索引的速度，
同時也發現trace flag 1117, 1118, 610在SQL Server 2016可以透過Alter database啟用，甚至trace flag 610已經啟用。

這次要驗證使用"Alter database"啟用trace flag 1117。

1.trace flag 1117官方說明。

DBCC TRACEON - Trace Flags (Transact-SQL)
Trace flag:1117
When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.

Note: Starting with SQL Server 2016 (13.x) this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no effect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

Scope: global only


2.建立測試db"TBTEST01"
command:
CREATE DATABASE [TBTEST01]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TBTEST01', FILENAME = N'E:\SQLData\TBTEST01.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'TBTEST01_log', FILENAME = N'E:\SQLLog\TBTEST01_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

3.增加新的Filegroup與Datafiles。
command:
USE [master]
GO
ALTER DATABASE [TBTEST01] ADD FILEGROUP [DATA]
GO
ALTER DATABASE [TBTEST01] ADD FILE ( NAME = N'TBTEST01_DATA01', FILENAME = N'E:\SQLData\TBTEST01_DATA01.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [DATA]
GO
ALTER DATABASE [TBTEST01] ADD FILE ( NAME = N'TBTEST01_DATA02', FILENAME = N'E:\SQLData\TBTEST01_DATA02.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [DATA]
GO

4.檢查測試資料庫Filegroup是否啟用auto growth
command:
USE [TBTEST01]
GO
SELECT
    DB_NAME() DBName,
    DBF.name AS FileName,
    FileGrp.name as DBFileGrpName,
    FileGrp.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileGrp
    ON DBF.data_space_id = FileGrp.data_space_id

5.測試資料庫Filegroup啟用auto growth
command:
USE [master]
GO
ALTER DATABASE [TBTEST01] MODIFY FILEGROUP [DATA] AUTOGROW_ALL_FILES;
GO

6.再次檢查測試資料庫Filegroup是否啟用auto growth
command:
USE [TBTEST01]
GO
SELECT
    DB_NAME() DBName,
    DBF.name AS FileName,
    FileGrp.name as DBFileGrpName,
    FileGrp.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileGrp
    ON DBF.data_space_id = FileGrp.data_space_id

7.在測試的DB與Filegroup "DATA"建立測試表並增加測資料
command:
USE [TBTEST01]
GO
CREATE TABLE test01
( id INT IDENTITY(1,1),
  Name VARCHAR(20)
) ON [DATA]
GO
USE [TBTEST01]
GO
INSERT INTO [TBTEST01].dbo.test01 VALUES ('Larry222222222222')
GO 3000000

結果:

8.依據結果只能說Filegroup大小相同，利用script來證明。
command:
USE [TBTEST01]
DECLARE @curr_tracefilename VARCHAR(500) ; 
DECLARE @base_tracefilename VARCHAR(500) ; 
DECLARE @indx INT ;

SELECT @curr_tracefilename = PATH FROM sys.traces WHERE is_default = 1 ; 
SET @curr_tracefilename = reverse(@curr_tracefilename);
SELECT @indx  = patindex('%\%', @curr_tracefilename) ;
SET @curr_tracefilename = reverse(@curr_tracefilename) ;
SET @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;  

SELECT
DatabaseName
,[Filename]
,(Duration/1000) AS [Duration(Secs)]
,StartTime
,EndTime
,(IntegerData*8.0/1024) AS ChangeInSize 
FROM ::fn_trace_gettable( @base_tracefilename, DEFAULT ) 
WHERE EventClass >=  92 AND EventClass <=  95 AND ServerName = @@servername AND DatabaseName = db_name()  
ORDER BY StartTime DESC;