Trace Flag 1117 and SQL Server 2019

on

For VLDBs I've always created multiple files for each file group and had to set the trace flag 1117 on to ensure that all files grow at the same time.

Since SQL Server 2016, the trace flag which makes all files in a filegroup grow was supposed to be built in, so we don't have to set the trace flag right?

The other day I set up 3 new filegroups, and set up 4 files for each file group, moved my data, grabbed a coffee, and sat down.

After my coffee, I checked on the filegroups and saw that they were not growing evenly! Wait, I thought I didn't need T1117.

Apparently, I don't, but now you need something else. Instead of adding -T1117 to your startup configuration parameters for SQL you now need to go to each file group in each database and 'tic' a box. 


So let me get this straight Microsoft, you built in T1117 but now instead of just fixing it once for a server we have to go to EACH database and EACH filegroup and mark the flag? Not helping, thanks.

Also, you can't just set the flag, there can't be any users connected to the database. You have to put the database in single-user mode, change the flags and then flip it back to multi-user mode. I had to disable users so they wouldn't connect, restart SQL, and then run this script before enabling the users again.

USE [master]
GO
ALTER DATABASE [MyDatabase] SET  SINGLE_USER WITH NO_WAIT
GO

USE [MyDatabase]
GO
declare @autogrow bit
SELECT @autogrow=convert(bit, is_autogrow_all_files) FROM sys.filegroups WHERE name=N'PRIMARY'
if(@autogrow=0)
	ALTER DATABASE [MyDatabase] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO

declare @autogrow bit
SELECT @autogrow=convert(bit, is_autogrow_all_files) FROM sys.filegroups WHERE name=N'FGBlob'
if(@autogrow=0)
	ALTER DATABASE [MyDatabase] MODIFY FILEGROUP [FGBlob] AUTOGROW_ALL_FILES
GO

declare @autogrow bit
SELECT @autogrow=convert(bit, is_autogrow_all_files) FROM sys.filegroups WHERE name=N'FGData'
if(@autogrow=0)
	ALTER DATABASE [MyDatabase] MODIFY FILEGROUP [FGData] AUTOGROW_ALL_FILES
GO

declare @autogrow bit
SELECT @autogrow=convert(bit, is_autogrow_all_files) FROM sys.filegroups WHERE name=N'FGIndexes'
if(@autogrow=0)
	ALTER DATABASE [MyDatabase] MODIFY FILEGROUP [FGIndexes] AUTOGROW_ALL_FILES
GO

USE [master]
GO
ALTER DATABASE [MyDatabase] SET  MULTI_USER WITH NO_WAIT
GO
After the script, now my files all grow at the same time.



Comments

Post a Comment