如何使用 T-SQL 向 SQL Server 数据库添加辅助数据文件

介绍如何在 SQL Server 中使用 T-SQL 的 ALTER DATABASE ADD FILE 语句向现有数据库添加辅助数据文件(.ndf),涵盖逻辑文件名、物理路径、初始大小及自动增长参数设置与验证。

发布于

在 SQL Server 中,数据库将其数据和对象(如表、视图、存储过程等)存储在操作系统文件中。默认情况下,使用标准参数创建数据库时,系统会生成一个主数据文件(Primary Data File,扩展名为 .mdf)和一个事务日志文件(Transaction Log File,扩展名为 .ldf)。

然而,数据库并不局限于单个数据文件。随着数据量的增长或出于 I/O 吞吐性能优化的考虑,数据库管理员可以在后续按需向数据库中添加辅助数据文件(Secondary Data File,扩展名通常为 .ndf)。

本文通过实际示例演示如何使用 T-SQL 向现有的 SQL Server 数据库添加辅助数据文件。

示例:向现有数据库添加数据文件

下面是使用 ALTER DATABASE ... ADD FILE 向现有数据库添加单个辅助数据文件的示例:

USE master;  
GO

ALTER DATABASE Solutions   
ADD FILE 
(  
    NAME = Solutions_dat_2,  
    FILENAME = '/var/opt/mssql/data/Solutions_dat_2.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5%  
);
GO

此操作将为 Solutions 数据库添加一个逻辑名称为 Solutions_dat_2、物理存储路径为 /var/opt/mssql/data/Solutions_dat_2.ndf 的新数据文件。

[!NOTE] 上例采用的是 Linux / macOS 容器环境下的文件路径规范。如果您的 SQL Server 部署在 Windows 操作系统上,物理路径应改为使用反斜杠(\),例如 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Solutions_dat_2.ndf。

参数说明

在 ADD FILE 子句中,各核心参数的含义如下:

  • NAME:指定文件的逻辑名称。这是在 SQL Server 实例内部执行管理语句(如备份、收缩或修改文件属性)时引用的标识符。
  • FILENAME:指定操作系统层面的物理文件完整路径(包含驱动器路径、文件名及扩展名)。请确保 SQL Server 运行账号对该目录拥有读写权限。
  • SIZE:指定数据文件的初始分配大小。可以使用 KB、MB、GB 或 TB 作为单位,未指定单位时默认采用 MB。
  • MAXSIZE:指定文件允许增长到的最大上限。可以以 KB、MB、GB、TB 为单位,也可以设为 UNLIMITED(无限制,直到磁盘耗尽)。
  • FILEGROWTH:指定数据文件的自动增长增量(当分配空间耗尽时,每次向系统申请追加的容量)。可以按百分比(如 5%)或固定容量(如 64MB)指定。

检查验证结果

添加完成后,可以通过查询当前数据库的 sys.database_files 系统目录视图来确认数据文件是否已成功挂载:

USE Solutions;  
GO

SELECT file_id, name, physical_name, state_desc, size * 8 / 1024 AS size_mb
FROM sys.database_files;  
GO

输出:

file_id  name             physical_name                            state_desc  size_mb
-------  ---------------  ---------------------------------------  ----------  -------
1        Solutions        /var/opt/mssql/data/Solutions.mdf        ONLINE      8
2        Solutions_log    /var/opt/mssql/data/Solutions_log.ldf    ONLINE      8
3        Solutions_dat_2  /var/opt/mssql/data/Solutions_dat_2.ndf  ONLINE      10

可以看到,新添加的数据文件 Solutions_dat_2 已经处于 ONLINE 状态并正式加入数据库存储池。

[!TIP] 如果日后该辅助数据文件不再使用,在清空其内部数据(通过 DBCC SHRINKFILE (Solutions_dat_2, EMPTYFILE))后,可使用 ALTER DATABASE Solutions REMOVE FILE Solutions_dat_2; 将其安全移除。