数据库文件和文件组Database Files and Filegroups

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

每个 SQL ServerSQL Server 数据库至少具有两个操作系统文件:一个数据文件和一个日志文件。At a minimum, every SQL ServerSQL Server database has two operating system files: a data file and a log file. 数据文件包含数据和对象,例如表、索引、存储过程和视图。Data files contain data and objects such as tables, indexes, stored procedures, and views. 日志文件包含恢复数据库中的所有事务所需的信息。Log files contain the information that is required to recover all transactions in the database. 为了便于分配和管理,可以将数据文件集合起来,放到文件组中。Data files can be grouped together in filegroups for allocation and administration purposes.

数据库文件Database Files

SQL ServerSQL Server 数据库具有三种类型的文件,如下表所示:databases have three types of files, as shown in the following table.

文件File 说明Description
Primary 包含数据库的启动信息,并指向数据库中的其他文件。Contains startup information for the database and points to the other files in the database. 每个数据库有一个主要数据文件。Every database has one primary data file. 主要数据文件的建议文件扩展名是 .mdf。The recommended file name extension for primary data files is .mdf.
辅助副本Secondary 用户定义的可选数据文件。Optional user-defined data files. 通过将每个文件放在不同的磁盘驱动器上,可将数据分散到多个磁盘中。Data can be spread across multiple disks by putting each file on a different disk drive. 次要数据文件的建议文件扩展名是 .ndf。The recommended file name extension for secondary data files is .ndf.
事务日志Transaction Log 此日志包含用于恢复数据库的信息。The log holds information used to recover the database. 每个数据库必须至少有一个日志文件。There must be at least one log file for each database. 事务日志的建议文件扩展名是 .ldf。The recommended file name extension for transaction logs is .ldf.

例如,简单数据库“Sales”包括一个包含所有数据和对象的主要文件和一个包含事务日志信息的日志文件。For example, a simple database named Sales has one primary file that contains all data and objects and a log file that contains the transaction log information. 可以创建一个更复杂的数据库“Orders”,其中包括一个主要文件和五个次要文件。A more complex database named Orders can be created that includes one primary file and five secondary files. 数据库中的数据和对象分散在所有六个文件中,而四个日志文件包含事务日志信息。The data and objects within the database spread across all six files, and the four log files contain the transaction log information.

默认情况下,数据和事务日志放在同一驱动器和路径上,以处理单磁盘系统。By default, the data and transaction logs are put on the same drive and path to handle single-disk systems. 对于生产环境,这可能不是最佳选择。This choice may not be optimal for production environments. 建议将数据和日志文件放在不同的磁盘上。We recommend that you put data and log files on separate disks.

逻辑和物理文件名称Logical and Physical File Names

SQL ServerSQL Server 文件具有两种文件名类型:files have two file name types:

logical_file_name: logical_file_name 是在所有 Transact-SQL 语句中引用物理文件时所使用的名称。logical_file_name: The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. 逻辑文件名必须符合 SQL ServerSQL Server 标识符规则,而且在数据库中的逻辑文件名中必须是唯一的。The logical file name must comply with the rules for SQL ServerSQL Server identifiers and must be unique among logical file names in the database.

os_file_name: os_file_name 是包括目录路径的物理文件的名称。os_file_name: The os_file_name is the name of the physical file including the directory path. 它必须符合操作系统文件命名规则。It must follow the rules for the operating system file names.

有关 NAMEFILENAME 参数的详细信息,请参阅 ALTER DATABASE 文件和文件组选项 (Transact-SQL)For more information on the NAME and FILENAME argument, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

重要

SQL ServerSQL Server 数据和日志文件可以保存在 FAT 或 NTFS 文件系统中。data and log files can be put on either FAT or NTFS file systems. 由于 NTFS 在安全方面具有优势,因此,建议在 Windows 系统上使用 NTFS 文件系统。On Windows systems, we recommend using the NTFS file system because the security aspects of NTFS.

警告

NTFS 压缩文件系统不支持读/写数据文件组和日志文件。Read/write data filegroups and log files are not supported on an NTFS compressed file system. 仅允许只读数据库和只读次要文件组保存在 NTFS 压缩文件系统中。Only read-only databases and read-only secondary filegroups are allowed to be put on an NTFS compressed file system. 为了节省空间,强烈建议使用数据压缩而不是文件系统压缩。For space savings, it is highly recommended to use data compression instead of file system compression.

如果多个 SQL ServerSQL Server 实例在一台计算机上运行,则每个实例都会接收到不同的默认目录来保存在该实例中创建的数据库文件。When multiple instances of SQL ServerSQL Server are running on a single computer, each instance receives a different default directory to hold the files for the databases created in the instance. 有关详细信息,请参阅 SQL Server 的默认实例和命名实例的文件位置For more information, see File Locations for Default and Named Instances of SQL Server.

数据文件页Data File Pages

SQL ServerSQL Server 数据文件中的页按顺序编号,文件的首页以 0 开始。Pages in a SQL ServerSQL Server data file are numbered sequentially, starting with zero (0) for the first page in the file. 数据库中的每个文件都有一个唯一的文件 ID 号。Each file in a database has a unique file ID number. 若要唯一标识数据库中的页,需要同时使用文件 ID 和页码。To uniquely identify a page in a database, both the file ID and the page number are required. 下例显示了包含 4-MB 主数据文件和 1-MB 次要数据文件的数据库中的页码。The following example shows the page numbers in a database that has a 4-MB primary data file and a 1-MB secondary data file.

data_file_pages

文件标头页是包含有关文件属性信息的首页。A file header page is the first page that contains information about the attributes of the file. 在文件开始处的其他几页也包含系统信息(例如分配映射)。Several of the other pages at the start of the file also contain system information, such as allocation maps. 有一个存储在主数据文件和第一个日志文件中的系统页是包含数据库属性信息的数据库引导页。One of the system pages stored in both the primary data file and the first log file is a database boot page that contains information about the attributes of the database.

文件大小File Size

SQL ServerSQL Server 文件可以从它们最初指定的大小开始自动增长。files can grow automatically from their originally specified size. 在定义文件时,您可以指定一个特定的增量。When you define a file, you can specify a specific growth increment. 每次填充文件时,其大小均按此增量来增长。Every time the file is filled, it increases its size by the growth increment. 如果文件组中有多个文件,则它们在所有文件被填满之前不会自动增长。If there are multiple files in a filegroup, they won't autogrow until all the files are full.

有关页和页类型的详细信息,请参阅页和盘区体系结构指南For more information about pages and page types, see Pages and Extents Architecture Guide.

每个文件还可以指定一个最大大小。Each file can also have a maximum size specified. 如果没有指定最大大小,文件可以一直增长到用完磁盘上的所有可用空间。If a maximum size isn't specified, the file can continue to grow until it has used all available space on the disk. 如果 SQL ServerSQL Server 作为数据库嵌入某应用程序,而该应用程序的用户无法迅速与系统管理员联系,则此功能就特别有用。This feature is especially useful when SQL ServerSQL Server is used as a database embedded in an application where the user doesn't have convenient access to a system administrator. 用户可以使文件根据需要自动增长,以减轻监视数据库中的可用空间和手动分配额外空间的管理负担。The user can let the files autogrow as required to reduce the administrative burden of monitoring free space in the database and manually allocating additional space.

有关事务日志文件管理的详细信息,请参阅管理事务日志文件的大小For more information on transaction log file management, see Manage the size of the transaction log file.

数据库快照文件Database Snapshot Files

数据库快照存储其“写入时复制”数据时所用的文件格式取决于快照是由用户创建,还是在内部使用:The form of file that is used by a database snapshot to store its copy-on-write data depends on whether the snapshot is created by a user or used internally:

  • 用户创建的数据库快照将其数据存储在一个或多个稀疏文件中。A database snapshot that is created by a user stores its data in one or more sparse files. 稀疏文件技术是 NTFS 文件系统的一项功能。Sparse file technology is a feature of the NTFS file system. 首先,稀疏文件不包含任何用户数据,并且没有为稀疏文件分配用于用户数据的磁盘空间。At first, a sparse file contains no user data, and disk space for user data hasn't been allocated to the sparse file. 若要大致了解如何在数据库快照中使用稀疏文件以及数据库快照如何增长,请参阅 查看数据库快照的稀疏文件的大小For general information about the use of sparse files in database snapshots and how database snapshots grow, see View the Size of the Sparse File of a Database Snapshot.
  • 数据库快照通过特定的 DBCC 命令在内部使用。Database snapshots are used internally by certain DBCC commands. 这些命令包括 DBCC CHECKDB、DBCC CHECKTABLE、DBCC CHECKALLOC 和 DBCC CHECKFILEGROUP。These commands include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKFILEGROUP. 内部数据库快照使用原始数据库文件的稀疏备用数据流。An internal database snapshot uses sparse alternate data streams of the original database files. 和稀疏文件一样,备用数据库流也是 NTFS 文件系统的一项功能。Like sparse files, alternate data streams are a feature of the NTFS file system. 使用稀疏备用数据流,可以进行多项数据分配,使其与单个文件或文件夹进行关联,但不影响文件大小或卷统计信息。The use of sparse alternate data streams allows for multiple data allocations to be associated with a single file or folder without affecting the file size or volume statistics.

文件组Filegroups

  • 此文件组包含主要数据文件和未放入其他文件组的所有次要文件。The filegroup contains the primary data file and any secondary files that aren't put into other filegroups.
  • 可以创建用户定义的文件组,用于将数据文件集合起来,以便于管理、数据分配和放置。User-defined filegroups can be created to group data files together for administrative, data allocation, and placement purposes.

例如:可以分别在三个磁盘驱动器上创建 Data1.ndfData2.ndfData3.ndf,然后将它们分配给文件组 fgroup1For example: Data1.ndf, Data2.ndf, and Data3.ndf, can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. 然后,可以明确地在文件组 fgroup1 上创建一个表。A table can then be created specifically on the filegroup fgroup1. 对表中数据的查询将分散到三个磁盘上,从而提高了性能。Queries for data from the table will be spread across the three disks; it will improve performance. 通过使用在 RAID(独立磁盘冗余阵列)条带集上创建的单个文件也能获得同样的性能提高。The same performance improvement can be accomplished by using a single file created on a RAID (redundant array of independent disks) stripe set. 但是,文件和文件组使您能够轻松地在新磁盘上添加新文件。However, files and filegroups let you easily add new files to new disks.

下表列出了存储在文件组中的所有数据文件。All data files are stored in the filegroups listed in the following table.

文件组Filegroup 说明Description
Primary 包含主要文件的文件组。The filegroup that contains the primary file. 所有系统表都是主要文件组的一部分。All system tables are part of the primary filegroup.
内存优化数据Memory Optimized Data 内存优化文件组基于 Filestream 文件组A memory-optimized filegroup is based on filestream filegroup
文件流Filestream
用户定义User-defined 用户首次创建数据库或以后修改数据库时创建的任何文件组。Any filegroup that is created by the user when the user first creates or later modifies the database.

默认 (Primary) 文件组Default (Primary) Filegroup

如果在数据库中创建对象时没有指定对象所属的文件组,对象将被分配给默认文件组。When objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup. 不管何时,只能将一个文件组指定为默认文件组。At any time, exactly one filegroup is designated as the default filegroup. 默认文件组中的文件必须足够大,能够容纳未分配给其他文件组的所有新对象。The files in the default filegroup must be large enough to hold any new objects not allocated to other filegroups.

PRIMARY 文件组是默认文件组,除非使用 ALTER DATABASE 语句进行了更改。The PRIMARY filegroup is the default filegroup unless it is changed by using the ALTER DATABASE statement. 但系统对象和表仍然分配给 PRIMARY 文件组,而不是新的默认文件组。Allocation for the system objects and tables remains within the PRIMARY filegroup, not the new default filegroup.

内存优化数据文件组Memory Optimized Data Filegroup

有关内存优化文件组的详细信息,请参阅内存优化文件组For more information on memory-optimized filegroups, see Memory Optimized Filegroup.

Filestream 文件组Filestream Filegroup

有关 Filestream 文件组的详细信息,请参阅 FILESTREAM创建启用了 FILESTREAM 的数据库For more information on filestream filegroups, see FILESTREAM and Create a FILESTREAM-Enabled Database.

文件和文件组示例File and Filegroup Example

以下示例在 SQL Server 实例上创建了一个数据库。The following example creates a database on an instance of SQL Server. 该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。The database has a primary data file, a user-defined filegroup, and a log file. 主数据文件在主文件组中,而用户定义文件组包含两个次要数据文件。The primary data file is in the primary filegroup and the user-defined filegroup has two secondary data files. ALTER DATABASE 语句将用户定义文件组指定为默认文件组。An ALTER DATABASE statement makes the user-defined filegroup the default. 然后通过指定用户定义文件组来创建表。A table is then created specifying the user-defined filegroup. (此示例使用通用路径 c:\Program Files\Microsoft SQL Server\MSSQL.1 来避免指定 SQL Server 版本。)(This example uses a generic path c:\Program Files\Microsoft SQL Server\MSSQL.1 to avoid specifying a version of SQL Server.)

USE master;
GO
-- Create the database with the default data
-- filegroup, filestream filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
  ( NAME='MyDB_Primary',
    FILENAME=
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
  ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
  ( NAME = 'MyDB_FG1_Dat2',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
  ( NAME = 'MyDB_FG_FS',
    FILENAME = 'c:\Data\filestream1')
LOG ON
  ( NAME='MyDB_log',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB 
  MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
  ( cola int PRIMARY KEY,
    colb char(8) )
ON MyDB_FG1;
GO

-- Create a table in the filestream filegroup
CREATE TABLE MyFSTable
(
    cola int PRIMARY KEY,
  colb VARBINARY(MAX) FILESTREAM NULL
)
GO

下图总结了上述示例的结果(Filestream 数据除外)。The following illustration summarizes the results of the previous example (except for the Filestream data).

filegroup_example

文件和文件组填充策略File and Filegroup Fill Strategy

文件组对组内的所有文件都使用按比例填充策略。Filegroups use a proportional fill strategy across all the files within each filegroup. 当数据写入文件组时,SQL Server 数据库引擎SQL Server Database Engine按文件中的可用空间比例将数据写入文件组中的每个文件,而不是将所有数据都写入第一个文件直至其变满为止。As data is written to the filegroup, the SQL Server 数据库引擎SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. 然后再写入下一个文件。It then writes to the next file. 例如,如果文件 f1 有 100 MB 可用空间,文件 f2 有 200 MB 可用空间,则从文件 f1 中提供一个盘区,从文件 f2 中提供两个盘区,依此类推。For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is given from file f1, two extents from file f2, and so on. 这样,两个文件几乎同时填满,并且可获得简单的条带化。In this way, both files become full at about the same time, and simple striping is achieved.

例如,某个文件组由三个文件组成,它们都设置为自动增长。For example, a filegroup is made up of three files, all set to automatically grow. 当文件组中所有文件的空间都已用完时,只扩展第一个文件。When space in all the files in the filegroup is exhausted, only the first file is expanded. 当第一个文件已满,无法再向文件组中写入更多数据时,将扩展第二个文件。When the first file is full and no more data can be written to the filegroup, the second file is expanded. 当第二个文件已满,无法再向文件组中写入更多数据时,将扩展第三个文件。When the second file is full and no more data can be written to the filegroup, the third file is expanded. 当第三个文件已满,无法再向文件组中写入更多数据时,将再次扩展第一个文件,依此类推。If the third file becomes full and no more data can be written to the filegroup, the first file is expanded again, and so on.

文件和文件组的设计规则Rules for designing Files and Filegroups

下列规则适用于文件和文件组:The following rules pertain to files and filegroups:

  • 一个文件或文件组不能由多个数据库使用。A file or filegroup cannot be used by more than one database. 例如,任何其他数据库都不能使用包含 sales 数据库中的数据和对象的文件 sales.mdf 和 sales.ndf。For example, file sales.mdf and sales.ndf, which contain data and objects from the sales database, can't be used by any other database.
  • 一个文件只能是一个文件组的成员。A file can be a member of only one filegroup.
  • 事务日志文件不能属于任何文件组。Transaction log files are never part of any filegroups.

建议Recommendations

使用文件和文件组时的建议:Recommendations when working with files and filegroups:

  • 大多数数据库在只有单个数据文件和单个事务日志文件的情况下性能良好。Most databases will work well with a single data file and a single transaction log file.
  • 如果使用多个数据文件,请为附加文件创建第二个文件组,并将其设置为默认文件组。If you use multiple data files, create a second filegroup for the additional file and make that filegroup the default filegroup. 这样,主文件将只包含系统表和对象。In this way, the primary file will contain only system tables and objects.
  • 若要使性能最大化,请在尽可能多的不同可用磁盘上创建文件或文件组。To maximize performance, create files or filegroups on different available disks as possible. 将争夺空间最激烈的对象置于不同的文件组中。Put objects that compete heavily for space in different filegroups.
  • 使用文件组将对象放置在特定的物理磁盘上。Use filegroups to enable placement of objects on specific physical disks.
  • 将在同一联接查询中使用的不同表置于不同的文件组中。Put different tables used in the same join queries in different filegroups. 由于采用并行磁盘 I/O 对联接数据进行搜索,所以此步骤可改善性能。This step will improve performance, because of parallel disk I/O searching for joined data.
  • 将最常访问的表和属于这些表的非聚集索引置于不同的文件组中。Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups. 如果文件位于不同的物理磁盘上,由于采用并行 I/O,所以使用不同的文件组可改善性能。Using different filegroups will improve performance, because of parallel I/O if the files are located on different physical disks.
  • 请勿将事务日志文件置于已有其他文件和文件组的同一物理磁盘上。Don't put the transaction log file(s) on the same physical disk that has the other files and filegroups.
  • 如果需要使用 Diskpart 等工具扩展数据库文件所在的卷或分区,则应备份所有系统数据库和用户数据库,还要先停止 SQL ServerSQL Server 服务。If you need to extend a volume or partition on which database files reside using tools like Diskpart, you should back up all system and user databases and stop SQL ServerSQL Server services first. 此外,磁盘卷成功扩展后,应考虑运行 DBCC CHECKDB 命令,确保驻留在该卷上的所有数据库的物理完整性。Also, once disk volumes are extended successfully, you should consider running DBCC CHECKDB command to ensure the physical integrity of all databases residing on the volume.

有关事务日志文件管理建议的详细信息,请参阅管理事务日志文件的大小For more information on transaction log file management recommendations, see Manage the size of the transaction log file.

CREATE DATABASE (SQL Server Transact-SQL) CREATE DATABASE (SQL Server Transact-SQL)
ALTER DATABASE 文件和文件组选项 (Transact-SQL) ALTER DATABASE File and Filegroup Options (Transact-SQL)
数据库分离和附加 (SQL Server)Database Detach and Attach (SQL Server)
SQL Server 事务日志体系结构和管理指南 SQL Server Transaction Log Architecture and Management Guide
页和区体系结构指南 Pages and Extents Architecture Guide
管理事务日志文件的大小Manage the size of the transaction log file