Manage Database files and file groups in SQLServer

SQLServer has three types of files

Primary – Primary data file contains the startup information of the database and also has information about other files in the database. The file extension is .mdf

Secondary – Secondary data file is used to store user data and these files are optional. The secondary files are used to spread data across multiple disks. The file extension is .ndf

Transaction log- Transaction log file holds the log information that is needed to recover the database. The file extension is .ldf

Every database has one primary file group and it is the best practice to create a user-defined file group for user data.

Create a user-defined file group:  Use below syntax to create user defined file group “USER_DATA”

USE master
GO
ALTER DATABASE TestDB
ADD FILEGROUP USER_DATA;
GO

ALTER DATABASE TestDB
ADD FILE
(
    NAME = USER_DATA1,
    FILENAME = ‘D: \DATA\user_data.ndf',
    SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB
),
TO FILEGROUP USER_DATA;
GO

You can also create a user-defined file group using Microsoft SQLServer Management Studio

1       . Right click on the database and choose Properties

2       . Go the FileGroups page, check on add and provide the file group name.

3       . Select the Default checkbox to make it as default file group

Modify User-defined/seconday file group as default:

USE master
GO
ALTER DATABASE [TestDB] MODIFY FILEGROUP [USER_DATA] DEFAULT

Please note that all the user objects will create in default file group.

Resize the file or Modifying a file:  Use below syntax to increase the max size of the file

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
    (NAME = USER_DATA1, MAXSIZE = 2000MB);
GO

Add a data file to existing file group:  Many of the users will come across this If you primary data file drive is out of space. Create a new drive and add a new ndf file to the file group.

USE [master]
GO
ALTER DATABASE [TestDB] ADD FILE ( NAME = N'USER_DATA2', FILENAME = N'E:\SQLData\user_data2.ndf' , SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB) TO FILEGROUP [USER_DATA]
GO

Moving a file to a new location: You must physically move the file to the new disk/direcotry before running this syntax.

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
( NAME = USERd_ATA2,
    FILENAME = N’E:\SQLData\user_data2.ndf'
); GO

Make sure to stop and start the SQL Server or take the database OFFLINE and then ONLINE To implement the change.

Removing a file from database: Use following syntax to remove a file from database

USE master;
GO
ALTER DATABASE TestDB
REMOVE FILE USER_DATA2;
GO