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
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
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
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
