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
GOAfter the script, now my files all grow at the same time.
Comments