learn about sqlserver files and filegroupThe filegroup is similar as tablespace in Oracle.
At first, I will show that hot to check file amd filegroup
check_filegroup.sql
select a.name as "File group",b.name as "file name", b.physical_name,size/128 as "MB",growth/128 as "growth MB"
from sys.data_spaces a inner join sys.database_files b
on a.data_space_id=b.data_space_id
go
Yes, you could check all information about file and filegroup. As we know, that is only one default file group "PRIMARY" at begining.
Here we go
alter database test add filegroup TESTFG_initial; ---- add new filegorup into database
alter database test add file (name = TESTFG_initial,filename = "C:\var\opt\mssql\data\TESTFG_initial.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_initial; --- add a new file into the filegroup
go
alter database test add filegroup TESTFG_201308;
alter database test add file (name = TESTFG_201308,filename = "C:\var\opt\mssql\data\TESTFG_201308.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201308;
go
alter database test add filegroup TESTFG_201309;
alter database test add file (name = TESTFG_201309,filename = "C:\var\opt\mssql\data\TESTFG_201309.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201309;
go
alter database test add filegroup TESTFG_201310;
alter database test add file (name = TESTFG_201310,filename = "C:\var\opt\mssql\data\TESTFG_201310.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201310;
go
alter database test add filegroup TESTFG_201311;
alter database test add file (name = TESTFG_201311,filename = "C:\var\opt\mssql\data\TESTFG_201311.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201311;
go
alter database test add filegroup TESTFG_201312;
alter database test add file (name = TESTFG_201312,filename = "C:\var\opt\mssql\data\TESTFG_201312.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201312;
go
alter database test add filegroup TESTFG_201401;
alter database test add file (name = TESTFG_201401,filename = "C:\var\opt\mssql\data\TESTFG_201401.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201401;
go
alter database test add filegroup TESTFG_201402;
alter database test add file (name = TESTFG_201402,filename = "C:\var\opt\mssql\data\TESTFG_201402.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201402;
go
alter database test add filegroup TESTFG_201403;
alter database test add file (name = TESTFG_201403,filename = "C:\var\opt\mssql\data\TESTFG_201403.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201403;
go
alter database test add filegroup TESTFG_201404;
alter database test add file (name = TESTFG_201404,filename = "C:\var\opt\mssql\data\TESTFG_201404.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201404;
go
alter database test add filegroup TESTFG_201405;
alter database test add file (name = TESTFG_201405,filename = "C:\var\opt\mssql\data\TESTFG_201405.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201405;
go
alter database test add filegroup TESTFG_201406;
alter database test add file (name = TESTFG_201406,filename = "C:\var\opt\mssql\data\TESTFG_201406.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201406;
go
alter database test add filegroup TESTFG_201407;
alter database test add file (name = TESTFG_201407,filename = "C:\var\opt\mssql\data\TESTFG_201407.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201407;
go
alter database test add filegroup TESTFG_201408;
alter database test add file (name = TESTFG_201408,filename = "C:\var\opt\mssql\data\TESTFG_201408.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201408;
go
alter database test add filegroup TESTFG_201409;
alter database test add file (name = TESTFG_201409,filename = "C:\var\opt\mssql\data\TESTFG_201409.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201409;
go
alter database test add filegroup TESTFG_201410;
alter database test add file (name = TESTFG_201410,filename = "C:\var\opt\mssql\data\TESTFG_201410.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201410;
go
alter database test add filegroup TESTFG_201411;
alter database test add file (name = TESTFG_201411,filename = "C:\var\opt\mssql\data\TESTFG_201411.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201411;
go
alter database test add filegroup TESTFG_201412;
alter database test add file (name = TESTFG_201412,filename = "C:\var\opt\mssql\data\TESTFG_201412.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201412;
go
alter database test add filegroup TESTFG_201501;
alter database test add file (name = TESTFG_201501,filename = "C:\var\opt\mssql\data\TESTFG_201501.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201501;
go
alter database test add filegroup TESTFG_201502;
alter database test add file (name = TESTFG_201502,filename = "C:\var\opt\mssql\data\TESTFG_201502.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201502;
go
alter database test add filegroup TESTFG_201503;
alter database test add file (name = TESTFG_201503,filename = "C:\var\opt\mssql\data\TESTFG_201503.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201503;
go
alter database test add filegroup TESTFG_201504;
alter database test add file (name = TESTFG_201504,filename = "C:\var\opt\mssql\data\TESTFG_201504.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201504;
go
alter database test add filegroup TESTFG_201505;
alter database test add file (name = TESTFG_201505,filename = "C:\var\opt\mssql\data\TESTFG_201505.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201505;
go
alter database test add filegroup TESTFG_201506;
alter database test add file (name = TESTFG_201506,filename = "C:\var\opt\mssql\data\TESTFG_201506.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201506;
go
alter database test add filegroup TESTFG_201507;
alter database test add file (name = TESTFG_201507,filename = "C:\var\opt\mssql\data\TESTFG_201507.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201507;
go
alter database test add filegroup TESTFG_201508;
alter database test add file (name = TESTFG_201508,filename = "C:\var\opt\mssql\data\TESTFG_201508.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201508;
go
alter database test add filegroup TESTFG_201509;
alter database test add file (name = TESTFG_201509,filename = "C:\var\opt\mssql\data\TESTFG_201509.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201509;
go
alter database test add filegroup TESTFG_201510;
alter database test add file (name = TESTFG_201510,filename = "C:\var\opt\mssql\data\TESTFG_201510.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201510;
go
alter database test add filegroup TESTFG_201511;
alter database test add file (name = TESTFG_201511,filename = "C:\var\opt\mssql\data\TESTFG_201511.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201511;
go
alter database test add filegroup TESTFG_201512;
alter database test add file (name = TESTFG_201512,filename = "C:\var\opt\mssql\data\TESTFG_201512.ndf",size = 1MB,maxsize = unlimited,filegrowth = 1MB) to filegroup TESTFG_201512;
go
Then what we see after it all finish.
Yes we do:
Comments