SQL Server 2016 – Trace Flag 1117 Changes

Hi Friends,

My today’s blog post is focused on trace flag 1117 changes in SQL Server 2016. We all know that there are multiple trace flags in SQL Server. These trace flags are generally used to change the default behavior of SQL Server. One of these trace flag is 1117. This trace flag is generally used to enable the growth for all the files under the file group. You can read the same in one of my previous blog post by clicking here.

In SQL Server 2016, you can not change that default behavior by using trace flag 1117. If you want to grow all the files under the file group at the same time, then you have to use the below command:

ALTER DATABASE [Database_Name] 
MODIFY FILEGROUP [FileGroup_Name] AUTOGROW_ALL_FILES;

Let me show you that the trace flag 1117 is not working with SQL Server 2016 R0:

USE [master]
GO
CREATE DATABASE [TestWith_1117]
ON  PRIMARY 
( NAME = N'TestWith_1117_1', FILENAME = N'D:\DATA\TestWith_1117_1.mdf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'TestWith_1117_2', FILENAME = N'D:\DATA\TestWith_1117_2.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'TestWith_1117_3', FILENAME = N'D:\DATA\TestWith_1117_3.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'TestWith_1117_4', FILENAME = N'D:\DATA\TestWith_1117_4.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB )
 LOG ON 
( NAME = N'TestWith_1117_log', FILENAME = N'D:\LOG\TestWith_1117_log.ldf' , SIZE = 4096KB , MAXSIZE = 4096MB , FILEGROWTH = 4096KB )
GO
use [TestWith_1117]
go
create table xtTestWith_1117
(
TestData char(8000)
)
go
Select name,(size*8)/1024 Size_MB from sys.database_files where type=0
go
DBCC TRACEON(1117,-1)
go
insert into xtTestWith_1117 values('Welcome to SQLServerGeeks.com')
go 3200
Select name,(size*8)/1024 Size_MB from sys.database_files where type=0
go
--as of now all data files are almost full here. if i will insert more rows then auto growth event will occur for all data files.
go 
insert into xtTestWith_1117 values('Welcome to SQLServerGeeks.com')
go 800
Select name,(size*8)/1024 Size_MB from sys.database_files where type=0
go
go
DBCC TRACEOFF(1117,-1)
Go

Trace Flag 1117 Change in SQL Server 2016

Now let me try to show you the auto grow all files in SQL Server 2016 R0 using Alter Table syntax. One of the major benefits of this syntax is: you can use the auto grow all files behavior for any specific file group and any specific database. In below TSQL example, I am going to create two file groups and I’ll also create two tables, one for each file group. I’ll enable the auto grow all files feature only for PRIMARY Filegroup:

USE [master]
GO
CREATE DATABASE [Test2016R0]
ON  PRIMARY 
( NAME = N'Test2016R0_1', FILENAME = N'D:\DATA\Test2016R0_1.mdf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'Test2016R0_2', FILENAME = N'D:\DATA\Test2016R0_2.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'Test2016R0_3', FILENAME = N'D:\DATA\Test2016R0_3.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'Test2016R0_4', FILENAME = N'D:\DATA\Test2016R0_4.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB )
 LOG ON 
( NAME = N'Test2016R0_log', FILENAME = N'D:\LOG\Test2016R0_log.ldf' , SIZE = 4096KB , MAXSIZE = 4096MB , FILEGROWTH = 4096KB )
GO
use [Test2016R0]
go
create table xtTest2016R0
(
TestData char(8000)
)
go
ALTER DATABASE [Test2016R0] 
MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;
go
insert into xtTest2016R0 values('Welcome to SQLServerGeeks.com')
go 3200
Select name,(size*8)/1024 Size_MB from sys.database_files where type=0
go
--as of now all data files are almost full here. if i will insert more rows then auto growth event will occur for all data files.
go 
insert into xtTest2016R0 values('Welcome to SQLServerGeeks.com')
go 800
Select name,(size*8)/1024 Size_MB from sys.database_files where type=0
GO
ALTER DATABASE [Test2016R0]
ADD FILEGROUP [SECONDARY];
GO
ALTER DATABASE [Test2016R0]
ADD FILE
( NAME = N'Test2016R0FG2_2_1', FILENAME = N'D:\DATA\Test2016R0FG2_2_1.mdf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'Test2016R0FG2_2_2', FILENAME = N'D:\DATA\Test2016R0FG2_2_2.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'Test2016R0FG2_2_3', FILENAME = N'D:\DATA\Test2016R0FG2_2_3.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB ),
( NAME = N'Test2016R0FG2_2_4', FILENAME = N'D:\DATA\Test2016R0FG2_2_4.ndf' , SIZE = 8 MB , MAXSIZE = 512 MB, FILEGROWTH = 8 MB )
TO FILEGROUP [SECONDARY];
Go
create table xtTest2016R0_2
(
TestData char(8000)
) ON [SECONDARY]
go
insert into xtTest2016R0_2 values('Welcome to SQLServerGeeks.com')
go 3200
Select name,(size*8)/1024 Size_MB from sys.database_files where type=0
go
--as of now all data files are almost full here. if i will insert more rows then auto growth event will occur for all data files.
go 
insert into xtTest2016R0_2 values('Welcome to SQLServerGeeks.com')
go 1000
Select name,(size*8)/1024 Size_MB from sys.database_files where type=0
Go

Trace Flag 1117 Change in SQL Server 2016 Filegroup Level

From the above output, you can see that auto growth for all files happen only in that file group for which we have enabled the auto grow all file settings.

Now if you want to check the name of file groups for which ‘autogrow_all_files’ is enabled then you can use the below TSQL syntax:

Use [Test2016R0]
GO
Select * from sys.filegroups
GO

AutoGrow_All_Files

PS: For tempdb database, behavior of trace flag 1117 is by default enabled. So, for tempdb in SQL Server 2016 there is no need to enable the TF 1117.

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *