DBAtlas · Database operations atlas

Add a data file to a database

SQL ServerStorage

Command

ALTER DATABASE [Customer]
ADD FILE (
  NAME = N'Customer_data2',
  FILENAME = N'E:\Data\Customer_data2.ndf',
  SIZE = 4GB,
  FILEGROWTH = 512MB,
  MAXSIZE = 100GB
) TO FILEGROUP [PRIMARY];

What it does

Adds a new data file (.ndf) to an existing filegroup to increase capacity and spread I/O.

When to use it

When the disk is full, or when you want to spread I/O across multiple disks.

Caution

Writes to the new file are balanced by proportional fill; the emptiest file fills first. Specify FILEGROWTH as fixed MB, not a percentage. Setting MAXSIZE prevents uncontrolled growth.

Open in DBAtlas →

Related