ALTER DATABASE (Transact-SQL) 文件和文件组选项ALTER DATABASE (Transact-SQL) File and Filegroup Options

修改与数据库关联的文件和文件组。Modifies the files and filegroups associated with the database. 在数据库中添加或删除文件和文件组,并更改数据库或其文件和文件组的属性。Adds or removes files and filegroups from a database, and changes the attributes of a database or its files and filegroups. 有关其他 ALTER DATABASE 选项,请参阅 ALTER DATABASEFor other ALTER DATABASE options, see ALTER DATABASE.

有关语法约定的详细信息,请参阅 Transact-SQL 语法约定For more information about the syntax conventions, see Transact-SQL syntax conventions.

单击一个产品Click a product

在下一行中,单击你感兴趣的产品名称。In the following row, click whichever product name you are interested in. 单击时此网页上的此位置会显示适合你单击的任何产品的不同内容。The click displays different content here on this webpage, appropriate for whichever product you click.

* SQL Server *  SQL Server SQL 数据库
托管实例
SQL Databasemanaged instance

 

语法Syntax

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ]
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ]
  | REMOVE FILE logical_file_name
  | MODIFY FILE <filespec>
}

<filespec>::=
(
    NAME = logical_file_name
    [ , NEWNAME = new_logical_name ]
    [ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
    [ , OFFLINE ]
)

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option>
        | DEFAULT
        | NAME = new_filegroup_name
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE }
    | { READ_ONLY | READ_WRITE }
}

参数Arguments

<add_or_modify_files>::=

add_or_modify_files>::=

指定要添加、删除或修改的文件。Specifies the file to be added, removed, or modified.

database_name 要修改的数据库的名称。 database_name Is the name of the database to be modified.

ADD FILE 向数据库中添加文件。ADD FILE Adds a file to the database.

TO FILEGROUP { filegroup_name } 指定要将指定文件添加到的文件组。TO FILEGROUP { filegroup_name } Specifies the filegroup to which to add the specified file. 若要显示当前文件组和当前的默认文件组,请使用 sys.filegroups 目录视图。To display the current filegroups and which filegroup is the current default, use the sys.filegroups catalog view.

ADD LOG FILE 将要添加的日志文件添加到指定的数据库。ADD LOG FILE Adds a log file be added to the specified database.

REMOVE FILE logical_file_name 从 SQL ServerSQL Server 的实例中删除逻辑文件说明并删除物理文件。REMOVE FILE logical_file_name Removes the logical file description from an instance of SQL ServerSQL Server and deletes the physical file. 除非文件为空,否则无法删除文件。The file cannot be removed unless it is empty.

logical_file_name 在 SQL ServerSQL Server 中引用文件时所用的逻辑名称。 logical_file_name Is the logical name used in when referencing the file.

警告

删除具有与其关联的 FILE_SNAPSHOT 备份的数据库文件将会成功,但不会删除任何关联的快照,以免使引用该数据库文件的备份失效。Removing a database file that has FILE_SNAPSHOT backups associated with it will succeed, but any associated snapshots will not be deleted to avoid invalidating the backups referring to the database file. 将截断该文件,但不会以物理方式将其删除,以保持 FILE_SNAPSHOT 备份不受影响。The file will be truncated, but will not be physically deleted in order to keep the FILE_SNAPSHOT backups intact. 有关详细信息,请参阅使用 Microsoft Azure Blob 存储服务执行 SQL Server 备份和还原For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. 适用于SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本)。 Applies to: ( and later).

MODIFY FILE 指定应修改的文件。MODIFY FILE Specifies the file that should be modified. 一次只能更改一个 <filespec> 属性。Only one <filespec> property can be changed at a time. 必须始终在 <filespec> 中指定 NAME,以标识要修改的文件。NAME must always be specified in the <filespec> to identify the file to be modified. 如果指定了 SIZE,那么新大小必须比文件当前大小要大。If SIZE is specified, the new size must be larger than the current file size.

若要修改数据文件或日志文件的逻辑名称,请在 NAME 子句中指定要重命名的逻辑文件名称,并在 NEWNAME 子句中指定文件的新逻辑名称。To modify the logical name of a data file or log file, specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. 例如:For example:

MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

若要将数据文件或日志文件移至新位置,请在 NAME 子句中指定当前的逻辑文件名称,并在 FILENAME 子句中指定新路径和操作系统文件名称。To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. 例如:For example:

MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

在移动全文目录时,请只在 FILENAME 子句中指定新路径。When you move a full-text catalog, specify only the new path in the FILENAME clause. 请不要指定操作系统文件名称。Do not specify the operating-system file name.

有关详细信息,请参阅移动数据库文件For more information, see Move Database Files.

对于 FILESTREAM 文件组,可以联机修改 NAME。For a FILESTREAM filegroup, NAME can be modified online. 可以联机修改 FILENAME;但是,只有在实际重新分配容器且关闭并重新启动服务器之后,所做的更改才生效。FILENAME can be modified online; however, the change does not take effect until after the container is physically relocated and the server is shutdown and then restarted.

可以将 FILESTREAM 文件设置为 OFFLINE。You can set a FILESTREAM file to OFFLINE. 在 FILESTREAM 文件处于脱机状态时,它的父文件组将在内部标记为脱机;因此,针对该文件组内 FILESTREAM 数据的所有访问均将失败。When a FILESTREAM file is offline, its parent filegroup will be internally marked as offline; therefore, all access to FILESTREAM data within that filegroup will fail.

备注

<add_or_modify_files> 选项在包含的数据库中不可用。<add_or_modify_files> options are not available in a Contained Database.

<filespec>::= file_spec

控制文件属性。Controls the file properties.

NAME logical_file_name 指定文件的逻辑名称。NAME logical_file_name Specifies the logical name of the file.

logical_file_name 在 SQL ServerSQL Server 的实例中引用文件时所用的逻辑名称。 logical_file_name Is the logical name used in an instance of when referencing the file.

NEWNAME new_logical_file_name 指定文件的新逻辑名称。NEWNAME new_logical_file_name Specifies a new logical name for the file.

new_logical_file_name 用于替换现有逻辑文件名称的名称。 new_logical_file_name Is the name to replace the existing logical file name. 该名称在数据库中必须唯一,并且必须符合标识符规则。The name must be unique within the database and comply with the rules for identifiers. 该名称可以是字符或 Unicode 常量、常规标识符或分隔标识符。The name can be a character or Unicode constant, a regular identifier, or a delimited identifier.

FILENAME { 'os_file_name' | '_filestream_path' | 'memory_optimized_data_path'} 指定操作系统(物理)文件名 _ FILENAME { 'os_file_name' | 'filestream_path' | 'memory_optimized_data_path'} Specifies the operating system (physical) file name.

' os_file_name ' 对于标准 (ROWS) 文件组,这是在创建文件时操作系统所使用的路径和文件名。' os_file_name ' For a standard (ROWS) filegroup, this is the path and file name that is used by the operating system when you create the file. 该文件必须驻留在安装 SQL ServerSQL Server 的服务器上。The file must reside on the server on which SQL ServerSQL Server is installed. 在执行 ALTER DATABASE 语句前,指定的路径必须已经存在。The specified path must exist before executing the ALTER DATABASE statement.

备注

当为文件指定了 UNC 路径时,无法设置 SIZEMAXSIZEFILEGROWTH 参数。SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set when a UNC path is specified for the file.

系统数据库不能位于 UNC 共享目录中。System databases cannot reside in UNC share directories.

不应将数据文件放在压缩文件系统中,除非这些文件是只读辅助文件或该数据库是只读的。Data files should not be put on compressed file systems unless the files are read-only secondary files, or if the database is read-only. 日志文件一定不要放在压缩文件系统中。Log files should never be put on compressed file systems.

如果文件位于原始分区上,则 os_file_name 必须仅指定现有原始分区的驱动器号。If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. 每个原始分区上只能存放一个文件。Only one file can be put on each raw partition.

' filestream_path ' 对于 FILESTREAM 文件组,FILENAME 指向将存储 FILESTREAM 数据的路径 。 ****'**** *filestream_path* **'** For a FILESTREAM filegroup, FILENAME refers to a path where FILESTREAM data will be stored. 在最后一个文件夹之前的路径必须存在,但不能存在最后一个文件夹。The path up to the last folder must exist, and the last folder must not exist. 例如,如果指定路径 C:\MyFiles\MyFilestreamData,则 C:\MyFiles 必须存在才能运行 ALTER DATABASE,但 MyFilestreamData 文件夹不能存在。For example, if you specify the path C:\MyFiles\MyFilestreamData, then C:\MyFiles must exist before you run ALTER DATABASE, but the MyFilestreamData folder must not exist.

备注

SIZE 和 FILEGROWTH 属性不适用于 FILESTREAM 文件组。The SIZE and FILEGROWTH properties do not apply to a FILESTREAM filegroup.

' memory_optimized_data_path ' 对于内存优化文件组,FILENAME 指向将存储内存优化数据的路径 。For a memory-optimized filegroup, FILENAME refers to a path where memory-optimized data will be stored. 在最后一个文件夹之前的路径必须存在,但不能存在最后一个文件夹。The path up to the last folder must exist, and the last folder must not exist. 例如,如果指定路径 C:\MyFiles\MyData,则 C:\MyFiles 必须存在才能运行 ALTER DATABASE,但 MyData 文件夹不能存在。For example, if you specify the path C:\MyFiles\MyData, then C:\MyFiles must exist before you run ALTER DATABASE, but the MyData folder must not exist.

必须在同一语句中创建文件组和文件 (<filespec>)。The filegroup and file (<filespec>) must be created in the same statement.

备注

SIZE、MAXSIZE 和 FILEGROWTH 属性不适用于 MEMORY_OPTIMIZED_DATA 文件组。The SIZE and FILEGROWTH properties do not apply to a MEMORY_OPTIMIZED_DATA filegroup.

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

SIZE size 指定文件大小。SIZE size Specifies the file size. SIZE 不适用于 FILESTREAM 文件组。SIZE does not apply to FILESTREAM filegroups.

size 文件的大小。 size Is the size of the file.

与 ADD FILE 一起指定时,size 是文件的初始大小。When specified with ADD FILE, size is the initial size for the file. 与 MODIFY FILE 一起指定时,size 是文件的新大小,而且必须大于文件的当前大小。When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.

如果没有为主文件提供 sizeSQL ServerSQL Server 会使用 model 数据库中主文件的大小。When size is not supplied for the primary file, the SQL ServerSQL Server uses the size of the primary file in the model database. 如果指定了辅助数据文件或日志文件,但未指定该文件的 size数据库引擎Database Engine会以 1 MB 作为该文件的大小。When a secondary data file or log file is specified but size is not specified for the file, the 数据库引擎Database Engine makes the file 1 MB.

后缀 KB、MB、GB 和 TB 可用于指定千字节、兆字节、千兆字节或兆兆字节。The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. 默认值为 MB。The default is MB. 指定整数,不包含小数。Specify a whole number and do not include a decimal. 若要指定兆字节的分数,请通过将数字乘以 1024 将该值转换为千字节。To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. 例如,应指定 1536 KB 而不是 1.5MB (1.5 x 1024 = 1536)。For example, specify 1536 KB instead of 1.5 MB (1.5 x 1024 = 1536).

备注

以下情况无法设置 SIZESIZE cannot be set:

  • 为文件指定了 UNC 路径时When a UNC path is specified for the file
  • 用于 FILESTREAMMEMORY_OPTIMIZED_DATA 文件组时For FILESTREAM and MEMORY_OPTIMIZED_DATA filegroups

MAXSIZE { max_size | UNLIMITED } 指定文件可增大到的最大文件大小。MAXSIZE { max_size| UNLIMITED } Specifies the maximum file size to which the file can grow.

max_size 最大的文件大小。 max_size Is the maximum file size. 后缀 KB、MB、GB 和 TB 可用于指定千字节、兆字节、千兆字节或兆兆字节。The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. 默认值为 MB。The default is MB. 指定整数,不包含小数。Specify a whole number and do not include a decimal. 如果未指定 max_size,文件大小将一直增长到磁盘变满为止。If max_size is not specified, the file size will increase until the disk is full.

UNLIMITED 指定文件将增长到磁盘充满。UNLIMITED Specifies that the file grows until the disk is full. SQL ServerSQL Server 中,指定为不限制增长的日志文件的最大大小为 2 TB,而数据文件的最大大小为 16 TB。In SQL ServerSQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB. 为 FILESTREAM 容器指定此选项时,没有最大大小。There is no maximum size when this option is specified for a FILESTREAM container. 它将继续增大,直到磁盘已满。It continues to grow until the disk is full.

备注

当为文件指定了 UNC 路径时无法设置 MAXSIZEMAXSIZE cannot be set when a UNC path is specified for the file.

FILEGROWTH growth_increment 指定文件的自动增量。FILEGROWTH growth_increment Specifies the automatic growth increment of the file. 文件的 FILEGROWTH 设置不能超过 MAXSIZE 设置。The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. FILEGROWTH 不适用于 FILESTREAM 文件组。FILEGROWTH does not apply to FILESTREAM filegroups.

growth_increment 每次需要新空间时为文件添加的空间量。 growth_increment Is the amount of space added to the file every time new space is required.

该值可以 MB、KB、GB、TB 或百分比 (%) 为单位指定。The value can be specified in MB, KB, GB, TB, or percent (%). 如果未在数量后面指定 MB、KB 或 %,则默认值为 MB。If a number is specified without an MB, KB, or % suffix, the default is MB. 如果指定 %,则增量大小为发生增长时文件大小的指定百分比。When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. 指定的大小舍入为最接近的 64 KB 的倍数。The size specified is rounded to the nearest 64 KB.

如果值为 0,则表明自动增长被设置为关闭,且不允许增加空间。A value of 0 indicates that automatic growth is set to off and no additional space is allowed.

如果未指定 FILEGROWTH,则默认值为:If FILEGROWTH is not specified, the default values are:

版本Version 默认值Default values
SQL Server 2016 (13.x)SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) 数据 64 MB。Data 64 MB. 日志文件 64 MB。Log files 64 MB.
SQL Server 2005 (9.x)SQL Server 2005 (9.x)Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x) 数据 1 MB。Data 1 MB. 日志文件 10%。Log files 10%.
SQL Server 2005 (9.x)SQL Server 2005 (9.x) 之前Prior to SQL Server 2005 (9.x)SQL Server 2005 (9.x) 数据 10%。Data 10%. 日志文件 10%。Log files 10%.

备注

以下情况无法设置 FILEGROWTHFILEGROWTH cannot be set:

  • 为文件指定了 UNC 路径时When a UNC path is specified for the file
  • 用于 FILESTREAMMEMORY_OPTIMIZED_DATA 文件组时For FILESTREAM and MEMORY_OPTIMIZED_DATA filegroups

OFFLINE 将文件设置为脱机并使文件组中的所有对象都不可访问。OFFLINE Sets the file offline and makes all objects in the filegroup inaccessible.

注意

仅当文件已损坏但可以还原时,才能使用该选项。Use this option only when the file is corrupted and can be restored. 对于设置为 OFFLINE 的文件,只有通过从备份中还原该文件,才能将其设置为联机。A file set to OFFLINE can only be set online by restoring the file from backup. 有关还原单个文件的详细信息,请参阅 RESTOREFor more information about restoring a single file, see RESTORE.

<filespec> 选项在包含的数据库中不可用。<filespec> options are not available in a Contained Database.

<add_or_modify_filegroups>::=

add_or_modify_filegroups>::=

在数据库中添加、修改或删除文件组。Add, modify, or remove a filegroup from the database.

ADD FILEGROUP filegroup_name 向数据库中添加文件组。ADD FILEGROUP filegroup_name Adds a filegroup to the database.

CONTAINS FILESTREAM 指定文件组在文件系统中存储 FILESTREAM 二进制大型对象 (BLOB)。CONTAINS FILESTREAM Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.

CONTAINS MEMORY_OPTIMIZED_DATACONTAINS MEMORY_OPTIMIZED_DATA

适用于SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本) Applies to: ( and later)

指定文件组在文件系统中存储内存优化数据。Specifies that the filegroup stores memory optimized data in the file system. 有关详细信息,请参阅内存中 OLTP - 内存中优化For more information, see In-Memory OLTP - In-Memory Optimization. 每个数据库只允许一个 MEMORY_OPTIMIZED_DATA 文件组。Only one MEMORY_OPTIMIZED_DATA filegroup is allowed per database. 在创建内存优化表时,文件组不能为空。For creating memory optimized tables, the filegroup cannot be empty. 其中必须至少包含一个文件。There must be at least one file. filegroup_name 引用一个路径。 filegroup_name refers to a path. 在最后一个文件夹之前的路径必须存在,但不能存在最后一个文件夹。The path up to the last folder must exist, and the last folder must not exist.

REMOVE FILEGROUP filegroup_name 从数据库中删除文件组。REMOVE FILEGROUP filegroup_name Removes a filegroup from the database. 除非文件组为空,否则无法将其删除。The filegroup cannot be removed unless it is empty. 首先从文件组中删除所有文件。Remove all files from the filegroup first. 有关详细信息,请参阅本主题前面的“REMOVE FILE logical_file_name”。For more information, see "REMOVE FILE logical_file_name," earlier in this topic.

备注

除非 FILESTREAM 垃圾回收器已从 FILESTREAM 容器中删除所有文件,否则删除 FILESTREAM 容器的 ALTER DATABASE REMOVE FILE 操作将失败并返回错误。Unless the FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the ALTER DATABASE REMOVE FILE operation to remove a FILESTREAM container will fail and return an error. 请参阅本主题后面的删除 FILESTREAM 容器部分。See the Removing a FILESTREAM Container section later in this topic.

MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME = new_filegroup_name } 通过将状态设置为 READ_ONLY 或 READ_WRITE、将文件组设置为数据库的默认文件组或者更改文件组名称来修改文件组。MODIFY FILEGROUP filegroup_name { filegroup_updatability_option> | DEFAULT | NAME newfilegroupname } Modifies the filegroup by setting the status to READ_ONLY or READ_WRITE, making the filegroup the default filegroup for the database, or changing the filegroup name.

<filegroup_updatability_option> 对文件组设置只读或读/写属性。<filegroup_updatability_option> Sets the read-only or read/write property to the filegroup.

DEFAULT 将默认数据库文件组改为 filegroup_name 。DEFAULT Changes the default database filegroup to filegroup_name. 数据库中只能有一个文件组作为默认文件组。Only one filegroup in the database can be the default filegroup. 有关详细信息,请参阅 数据库文件和文件组For more information, see Database Files and Filegroups.

NAME = new_filegroup_name 将文件组名称改为 new_filegroup_name 。NAME = new_filegroup_name Changes the filegroup name to the new_filegroup_name.

AUTOGROW_SINGLE_FILE 适用于 :SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本)AUTOGROW_SINGLE_FILE Applies to: ( and later)

当文件组中的某个文件达到自动增长阈值时,只有该文件会增长。When a file in the filegroup meets the autogrow threshold, only that file grows. 这是默认值。This is the default.

AUTOGROW_ALL_FILESAUTOGROW_ALL_FILES

适用于SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本) Applies to: ( and later).

当文件组中的某个文件达到自动增长阈值时,文件组中的所有文件都会增长。When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.

备注

这是 TempDB 的默认值。This is the default value for TempDB.

<filegroup_updatability_option>::= filegroup_updatability_option>

对文件组设置只读或读/写属性。Sets the read-only or read/write property to the filegroup.

READ_ONLY | READONLY 指定文件组为只读。READ_ONLY | READONLY Specifies the filegroup is read-only. 不允许更新其中的对象。Updates to objects in it are not allowed. 主文件组不能设置为只读。The primary filegroup cannot be made read-only. 若要更改此状态,您必须对数据库有独占访问权限。To change this state, you must have exclusive access to the database. 有关详细信息,请参阅 SINGLE_USER 子句。For more information, see the SINGLE_USER clause.

因为只读数据库不允许数据修改,所以将发生以下情况:Because a read-only database does not allow data modifications:

  • 系统启动时,将跳过自动恢复。Automatic recovery is skipped at system startup.
  • 不能收缩数据库。Shrinking the database is not possible.
  • 在只读数据库中不会进行锁定。No locking occurs in read-only databases. 这可以加快查询速度。This can cause faster query performance.

备注

MicrosoftMicrosoftSQL ServerSQL Server 的未来版本中,将删除 READONLY 关键字。The keyword READONLY will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新的开发工作中使用 READONLY,并计划修改当前使用 READONLY 的应用程序。Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. 请改用 READ_ONLYUse READ_ONLY instead.

READ_WRITE | READWRITE 指定组是 READ_WRITE。READ_WRITE | READWRITE Specifies the group is READ_WRITE. 允许更新文件组中的对象。Updates are enabled for the objects in the filegroup. 若要更改此状态,您必须对数据库有独占访问权限。To change this state, you must have exclusive access to the database. 有关详细信息,请参阅 SINGLE_USER 子句。For more information, see the SINGLE_USER clause.

备注

MicrosoftMicrosoftSQL ServerSQL Server 的未来版本中,将删除 READWRITE 关键字。The keyword READWRITE will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新的开发工作中使用 READWRITE,并计划将当前使用 READWRITE 的应用程序修改为使用 READ_WRITEAvoid using READWRITE in new development work, and plan to modify applications that currently use READWRITE to use READ_WRITE instead.

提示

可通过查看 sys.databases 目录视图中的is_read_only 列,或者查看 DATABASEPROPERTYEX 函数的 Updateability 属性,来确定这些选项的状态。The status of these options can be determined by examining the is_read_only column in the sys.databases catalog view or the Updateability property of the DATABASEPROPERTYEX function.

备注Remarks

若要减小数据库的大小,请使用 DBCC SHRINKDATABASETo decrease the size of a database, use DBCC SHRINKDATABASE.

BACKUP 语句正在运行时,不能添加或删除文件。You cannot add or remove a file while a BACKUP statement is running.

可以为每个数据库指定最多 32,767 个文件和 32,767 个文件组。A maximum of 32,767 files and 32,767 filegroups can be specified for each database.

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 开始,对数据库文件状态(例如,联机或脱机)的维护是独立于数据库状态的。Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x), the state of a database file (for example, online or offline), is maintained independently from the state of the database. 有关详细信息,请参阅文件状态For more information, see File States.

  • 文件组中文件的状态决定整个文件组的可用性。The state of the files within a filegroup determines the availability of the whole filegroup. 文件组中的所有文件都必须联机,文件组才可用。For a filegroup to be available, all files within the filegroup must be online.
  • 如果文件组脱机,则使用 SQL 语句访问文件组的所有尝试都会失败并报告错误。If a filegroup is offline, any try to access the filegroup by an SQL statement will fail with an error. 在为 SELECT 语句生成查询计划时,查询优化器会避免使用驻留在脱机文件组中的非聚集索引和索引视图。When you build query plans for SELECT statements, the query optimizer avoids nonclustered indexes and indexed views that reside in offline filegroups. 这样,这些语句就会成功。This enables these statements to succeed. 但是,如果脱机文件组包含目标表的堆或聚集索引,SELECT 语句将失败。However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. 此外,如果 INSERTUPDATEDELETE 语句修改的表的索引包含在脱机文件组中,这些语句将失败。Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup will fail.

如果为该文件指定了 UNC 路径,则无法设置 SIZE、MAXSIZE 和 FILEGROWTH 参数。SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set when a UNC path is specified for the file.

无法为内存优化文件组设置 SIZE 和 FILEGROWTH 参数。SIZE and FILEGROWTH parameters cannot be set for memory optimized filegroups.

MicrosoftMicrosoftSQL ServerSQL Server 的未来版本中,将删除 READONLY 关键字。The keyword READONLY will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新的开发工作中使用 READONLY,并计划修改当前使用 READONLY 的应用程序。Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. 请改用 READ_ONLYUse READ_ONLY instead.

MicrosoftMicrosoftSQL ServerSQL Server 的未来版本中,将删除 READWRITE 关键字。The keyword READWRITE will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新的开发工作中使用 READWRITE,并计划将当前使用 READWRITE 的应用程序修改为使用 READ_WRITEAvoid using READWRITE in new development work, and plan to modify applications that currently use READWRITE to use READ_WRITE instead.

移动文件Moving Files

您可通过在 FILENAME 中指定新位置来移动系统或用户定义的数据和日志文件。You can move system or user-defined data and log files by specifying the new location in FILENAME. 这在下列情况下可能很有用:This may be useful in the following scenarios:

  • 故障恢复。Failure recovery. 例如,数据库处于可疑模式或因硬件故障而关闭。For example, the database is in suspect mode or shutdown caused by hardware failure.
  • 预先安排的重定位。Planned relocation.
  • 为预定的磁盘维护操作而进行的重定位。Relocation for scheduled disk maintenance.

有关详细信息,请参阅移动数据库文件For more information, see Move Database Files.

初始化文件Initializing Files

默认情况下,在执行下列操作之一时,将通过在文件中填充零来初始化数据和日志文件。By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:

  • 创建数据库。Create a database.
  • 向现有数据库添加文件。Add files to an existing database.
  • 增加现有文件的大小。Increase the size of an existing file.
  • 还原数据库或文件组。Restore a database or filegroup.

可以在瞬间对数据文件进行初始化。Data files can be initialized instantaneously. 这样,可以快速执行这些文件操作。This enables for fast execution of these file operations. 有关详细信息,请参阅 数据库文件初始化For more information, see Database File Initialization.

删除 FILESTREAM 容器

Removing a FILESTREAM Container

即使已使用“DBCC SHRINKFILE”操作清空 FILESTREAM 容器,但出于各种系统维护原因,数据库可能仍然需要保留对已删除文件的引用。Even though FILESTREAM container may have been emptied using the "DBCC SHRINKFILE" operation, the database may still need to maintain references to the deleted files for various system maintenance reasons. sp_filestream_force_garbage_collection 会运行 FILESTREAM 垃圾回收器以删除这些文件,以便可安全地执行此操作。 sp_filestream_force_garbage_collection will run the FILESTREAM Garbage Collector to remove these files when it is safe to do so. 除非 FILESTREAM 垃圾回收器已从 FILESTREAM 容器中删除所有文件,否则 ALTER DATABASE REMOVE FILE 操作将无法删除 FILESTREAM 容器并返回错误。Unless the FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the ALTER DATABASE REMOVE FILE operation will fail to remove a FILESTREAM container and will return an error. 建议使用以下过程删除 FILESTREAM 容器。The following process is recommended to remove a FILESTREAM container.

  1. 运行带有 EMPTYFILE 选项的 DBCC SHRINKFILE,将此容器的活动内容移动到其他容器。Run DBCC SHRINKFILE with the EMPTYFILE option to move the active contents of this container to other containers.
  2. 确保已在 FULL 或 BULK_LOGGED 恢复模型中执行日志备份。Ensure that Log backups have been taken, in the FULL or BULK_LOGGED recovery model.
  3. 确保复制日志读取器作业已运行(如果相关)。Ensure that the replication log reader job has been run, if relevant.
  4. 运行 sp_filestream_force_garbage_collection 以强制垃圾回收器删除此容器中不再需要的任何文件。Run sp_filestream_force_garbage_collection to force the garbage collector to delete any files that are no longer needed in this container.
  5. 执行带有 REMOVE FILE 选项的 ALTER DATABASE,以删除此容器。Execute ALTER DATABASE with the REMOVE FILE option to remove this container.
  6. 再重复一次步骤 2 到 4,以完成垃圾回收。Repeat steps 2 through 4 once more to complete the garbage collection.
  7. 使用 ALTER Database...REMOVE FILE 删除此容器。Use ALTER Database...REMOVE FILE to remove this container.

示例Examples

A.A. 向数据库中添加文件Adding a file to a database

以下示例将一个 5 MB 的数据文件添加到 AdventureWorks2012AdventureWorks2012 数据库。The following example adds a 5-MB data file to the AdventureWorks2012AdventureWorks2012 database.

USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B.B. 向数据库中添加由两个文件组成的文件组Adding a filegroup with two files to a database

以下示例在 AdventureWorks2012AdventureWorks2012 数据库中创建文件组 Test1FG1,然后将两个 5 MB 的文件添加到该文件组。The following example creates the filegroup Test1FG1 in the AdventureWorks2012AdventureWorks2012 database and adds two 5-MB files to the filegroup.

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),  
(  
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

C.C. 向数据库中添加两个日志文件Adding two log files to a database

下面的示例向 AdventureWorks2012AdventureWorks2012 数据库中添加两个 5 MB 的日志文件。The following example adds two 5-MB log files to the AdventureWorks2012AdventureWorks2012 database.

USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD LOG FILE
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D.D. 从数据库中删除文件Removing a file from a database

以下示例删除示例 B 中添加的一个文件。The following example removes one of the files added in example B.

USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO

E.E. 修改文件Modifying a file

以下示例增加示例 B 中添加的一个文件的大小。带有 MODIFY FILE 命令的 ALTER DATABASE 只能增大文件大小,因此,如果需要缩小文件大小,则需使用 DBCC SHRINKFILE。The following example increases the size of one of the files added in example B. The ALTER DATABASE with MODIFY FILE command can only make a file size bigger, so if you need to make the file size smaller you need to use DBCC SHRINKFILE.

USE master;
GO

ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

此示例将数据文件的大小缩小为 100 MB,然后指定该数量的大小。This example shrinks the size of a data file to 100 MB, and then specifies the size at that amount.

USE AdventureWorks2012;
GO

DBCC SHRINKFILE (AdventureWorks2012_data, 100);
GO

USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

F.F. 将文件移至新位置Moving a file to a new location

以下示例将在示例 A 中创建的 Test1dat2 文件移至新目录中。The following example moves the Test1dat2 file created in example A to a new directory.

备注

必须先将该文件实际移至新目录中,然后才能运行此示例。You must physically move the file to the new directory before running this example. 然后,停止和启动 SQL ServerSQL Server 的实例,或使 AdventureWorks2012AdventureWorks2012 数据库 OFFLINE 再 ONLINE,以实施更改。Afterward, stop and start the instance of SQL ServerSQL Server or take the AdventureWorks2012AdventureWorks2012 database OFFLINE and then ONLINE to implement the change.

USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G.G. 将 tempdb 移至新位置Moving tempdb to a new location

以下示例将 tempdb 从其在磁盘上的当前位置移至另一个磁盘位置。The following example moves tempdb from its current location on the disk to another disk location. 由于每次启动 MSSQLSERVER 服务时都会重新创建 tempdb,因此您不必实际移动数据和日志文件。Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. 在步骤 3 中重新启动服务时,将创建这些文件。The files are created when the service is restarted in step 3. 在重新启动该服务之前,tempdb 将继续在现有位置发挥作用。Until the service is restarted, tempdb continues to function in its existing location.

  1. 确定 tempdb 数据库的逻辑文件名称以及这些文件在磁盘上的当前位置。Determine the logical file names of the tempdb database and their current location on disk.

    SELECT name, physical_name
    FROM sys.master_files
    WHERE database_id = DB_ID('tempdb');
    GO
    
  2. 使用 ALTER DATABASE更改每个文件的位置。Change the location of each file by using ALTER DATABASE.

    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
    GO
    
  3. 停止再重新启动 SQL ServerSQL Server的实例。Stop and restart the instance of SQL ServerSQL Server.

  4. 验证文件更改。Verify the file change.

    SELECT name, physical_name
    FROM sys.master_files
    WHERE database_id = DB_ID('tempdb');
    
  5. 将 tempdb.mdf 和 templog.ldf 文件从其原始位置中删除。Delete the tempdb.mdf and templog.ldf files from their original location.

H.H. 使文件组成为默认文件组Making a filegroup the default

以下示例使示例 B 中创建的 Test1FG1 文件组成为默认文件组。The following example makes the Test1FG1 filegroup created in example B the default filegroup. 然后,默认文件组被重置为 PRIMARY 文件组。Then, the default filegroup is reset to the PRIMARY filegroup. 请注意,必须使用括号或引号分隔 PRIMARYNote that PRIMARY must be delimited by brackets or quotation marks.

USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I.I. 使用 ALTER DATABASE 添加文件组Adding a Filegroup Using ALTER DATABASE

以下示例将一个包含 FILEGROUP 子句的 FILESTREAM 添加到 FileStreamPhotoDB 数据库。The following example adds a FILEGROUP that contains the FILESTREAM clause to the FileStreamPhotoDB database.

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause.
ALTER DATABASE FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM;
GO

--Add a file for storing database photos to FILEGROUP
ALTER DATABASE FileStreamPhotoDB
ADD FILE
(
  NAME= 'PhotoShoot1',
  FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot;
GO

以下示例将一个包含 FILEGROUP 子句的 MEMORY_OPTIMIZED_DATA 添加到 xtp_db 数据库。The following example adds a FILEGROUP that contains the MEMORY_OPTIMIZED_DATA clause to the xtp_db database. 该文件组存储内存优化数据。The filegroup stores memory optimized data.

--Create and add a FILEGROUP that CONTAINS the MEMORY_OPTIMIZED_DATA clause.
ALTER DATABASE xtp_db
ADD FILEGROUP xtp_fg
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

--Add a file for storing memory optimized data to FILEGROUP
ALTER DATABASE xtp_db
ADD FILE
(
  NAME='xtp_mod',
  FILENAME='d:\data\xtp_mod'
)
TO FILEGROUP xtp_fg;
GO

J.J. 更改文件组,以便当文件组中的某个文件达到自动增长阈值时,文件组中的所有文件都会增长Change filegroup so that when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow

以下示例会生成所需的 ALTER DATABASE 语句,以使用 AUTOGROW_ALL_FILES 设置修改读写文件组。The following example generates the required ALTER DATABASE statements to modify read-write filegroups with the AUTOGROW_ALL_FILES setting.

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements
--so that all read-write filegroups grow at the same time.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, isdone bit);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, fgname sysname, isdone bit);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid int, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
  SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

  SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
  INSERT INTO #tmpfgs
  EXEC (@query)

  UPDATE #tmpdbs
  SET isdone = 1
  WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
  WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
  BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

    SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

    PRINT @query

    UPDATE #tmpfgs
    SET isdone = 1
    WHERE [dbid] = @dbid AND fgname = @fgname
  END
END;
GO

另请参阅See Also

SQL ServerSQL Server *SQL 数据库
托管实例*

SQL Databasemanaged instance
 |

 

Azure SQL 数据库托管实例Azure SQL Database managed instance

对 Azure SQL 数据库托管实例中的数据库使用此语句。Use this statement with a database in Azure SQL Database managed instance.

托管实例中的数据库的语法Syntax for databases in a managed instance

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ]
        [ TO FILEGROUP { filegroup_name } ]
  | REMOVE FILE logical_file_name
  | MODIFY FILE <filespec>
}

<filespec>::=
(
    NAME = logical_file_name
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
)

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option>
        | DEFAULT
        | NAME = new_filegroup_name
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }
        }
}  
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE }
    | { READ_ONLY | READ_WRITE }
}

参数Arguments

<add_or_modify_files>::=

add_or_modify_files>::=

指定要添加、删除或修改的文件。Specifies the file to be added, removed, or modified.

database_name 要修改的数据库的名称。 database_name Is the name of the database to be modified.

ADD FILE 向数据库中添加文件。ADD FILE Adds a file to the database.

TO FILEGROUP { filegroup_name } 指定要将指定文件添加到的文件组。TO FILEGROUP { filegroup_name } Specifies the filegroup to which to add the specified file. 若要显示当前文件组和当前的默认文件组,请使用 sys.filegroups 目录视图。To display the current filegroups and which filegroup is the current default, use the sys.filegroups catalog view.

REMOVE FILE logical_file_name 从 SQL ServerSQL Server 的实例中删除逻辑文件说明并删除物理文件。REMOVE FILE logical_file_name Removes the logical file description from an instance of SQL ServerSQL Server and deletes the physical file. 除非文件为空,否则无法删除文件。The file cannot be removed unless it is empty.

logical_file_name 在 SQL ServerSQL Server 中引用文件时所用的逻辑名称。 logical_file_name Is the logical name used in when referencing the file.

MODIFY FILE 指定应修改的文件。MODIFY FILE Specifies the file that should be modified. 一次只能更改一个 <filespec> 属性。Only one <filespec> property can be changed at a time. 必须始终在 <filespec> 中指定 NAME,以标识要修改的文件。NAME must always be specified in the <filespec> to identify the file to be modified. 如果指定了 SIZE,那么新大小必须比文件当前大小要大。If SIZE is specified, the new size must be larger than the current file size.

<filespec>::= file_spec

控制文件属性。Controls the file properties.

NAME logical_file_name 指定文件的逻辑名称。NAME logical_file_name Specifies the logical name of the file.

logical_file_name 在 SQL ServerSQL Server 的实例中引用文件时所用的逻辑名称。 logical_file_name Is the logical name used in an instance of when referencing the file.

NEWNAME new_logical_file_name 指定文件的新逻辑名称。NEWNAME new_logical_file_name Specifies a new logical name for the file.

new_logical_file_name 用于替换现有逻辑文件名称的名称。 new_logical_file_name Is the name to replace the existing logical file name. 该名称在数据库中必须唯一,并且必须符合标识符规则。The name must be unique within the database and comply with the rules for identifiers. 该名称可以是字符或 Unicode 常量、常规标识符或分隔标识符。The name can be a character or Unicode constant, a regular identifier, or a delimited identifier.

SIZE size 指定文件大小。SIZE size Specifies the file size.

size 文件的大小。 size Is the size of the file.

与 ADD FILE 一起指定时,size 是文件的初始大小。When specified with ADD FILE, size is the initial size for the file. 与 MODIFY FILE 一起指定时,size 是文件的新大小,而且必须大于文件的当前大小。When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.

如果没有为主文件提供 sizeSQL ServerSQL Server 会使用 model 数据库中主文件的大小。When size is not supplied for the primary file, the SQL ServerSQL Server uses the size of the primary file in the model database. 如果指定了辅助数据文件或日志文件,但未指定该文件的 size数据库引擎Database Engine会以 1 MB 作为该文件的大小。When a secondary data file or log file is specified but size is not specified for the file, the 数据库引擎Database Engine makes the file 1 MB.

后缀 KB、MB、GB 和 TB 可用于指定千字节、兆字节、千兆字节或兆兆字节。The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. 默认值为 MB。The default is MB. 指定整数,不包含小数。Specify a whole number and do not include a decimal. 若要指定兆字节的分数,请通过将数字乘以 1024 将该值转换为千字节。To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. 例如,应指定 1536 KB 而不是 1.5MB (1.5 x 1024 = 1536)。For example, specify 1536 KB instead of 1.5 MB (1.5 x 1024 = 1536).

MAXSIZE { max_size | UNLIMITED } 指定文件可增大到的最大文件大小。MAXSIZE { max_size| UNLIMITED } Specifies the maximum file size to which the file can grow.

max_size 最大的文件大小。 max_size Is the maximum file size. 后缀 KB、MB、GB 和 TB 可用于指定千字节、兆字节、千兆字节或兆兆字节。The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. 默认值为 MB。The default is MB. 指定整数,不包含小数。Specify a whole number and do not include a decimal. 如果未指定 max_size,文件大小将一直增长到磁盘变满为止。If max_size is not specified, the file size will increase until the disk is full.

UNLIMITED 指定文件将增长到磁盘充满。UNLIMITED Specifies that the file grows until the disk is full. SQL ServerSQL Server 中,指定为不限制增长的日志文件的最大大小为 2 TB,而数据文件的最大大小为 16 TB。In SQL ServerSQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.

FILEGROWTH growth_increment 指定文件的自动增量。FILEGROWTH growth_increment Specifies the automatic growth increment of the file. 文件的 FILEGROWTH 设置不能超过 MAXSIZE 设置。The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.

growth_increment 每次需要新空间时为文件添加的空间量。 growth_increment Is the amount of space added to the file every time new space is required.

该值可以 MB、KB、GB、TB 或百分比 (%) 为单位指定。The value can be specified in MB, KB, GB, TB, or percent (%). 如果未在数量后面指定 MB、KB 或 %,则默认值为 MB。If a number is specified without an MB, KB, or % suffix, the default is MB. 如果指定 %,则增量大小为发生增长时文件大小的指定百分比。When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. 指定的大小舍入为最接近的 64 KB 的倍数。The size specified is rounded to the nearest 64 KB.

如果值为 0,则表明自动增长被设置为关闭,且不允许增加空间。A value of 0 indicates that automatic growth is set to off and no additional space is allowed.

如果未指定 FILEGROWTH,则默认值为:If FILEGROWTH is not specified, the default values are:

  • 数据 64 MBData 64 MB
  • 日志文件 64 MBLog files 64 MB

<add_or_modify_filegroups>::=

add_or_modify_filegroups>::=

在数据库中添加、修改或删除文件组。Add, modify, or remove a filegroup from the database.

ADD FILEGROUP filegroup_name 向数据库中添加文件组。ADD FILEGROUP filegroup_name Adds a filegroup to the database.

下面的示例创建添加到一个添加到名为 sql_db_mi 的数据库的文件组,然后向该文件组添加一个文件。The following example creates a filegroup that is added to a database named sql_db_mi, and adds a file to the filegroup.

ALTER DATABASE sql_db_mi ADD FILEGROUP sql_db_mi_fg;
GO
ALTER DATABASE sql_db_mi ADD FILE (NAME='sql_db_mi_mod') TO FILEGROUP sql_db_mi_fg;

REMOVE FILEGROUP filegroup_name 从数据库中删除文件组。REMOVE FILEGROUP filegroup_name Removes a filegroup from the database. 除非文件组为空,否则无法将其删除。The filegroup cannot be removed unless it is empty. 首先从文件组中删除所有文件。Remove all files from the filegroup first. 有关详细信息,请参阅本主题前面的“REMOVE FILE logical_file_name”。For more information, see "REMOVE FILE logical_file_name," earlier in this topic.

MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME = new_filegroup_name } 通过将状态设置为 READ_ONLY 或 READ_WRITE、将文件组设置为数据库的默认文件组或者更改文件组名称来修改文件组。MODIFY FILEGROUP filegroup_name { filegroup_updatability_option> | DEFAULT | NAME newfilegroupname } Modifies the filegroup by setting the status to READ_ONLY or READ_WRITE, making the filegroup the default filegroup for the database, or changing the filegroup name.

<filegroup_updatability_option> 对文件组设置只读或读/写属性。<filegroup_updatability_option> Sets the read-only or read/write property to the filegroup.

DEFAULT 将默认数据库文件组改为 filegroup_name 。DEFAULT Changes the default database filegroup to filegroup_name. 数据库中只能有一个文件组作为默认文件组。Only one filegroup in the database can be the default filegroup. 有关详细信息,请参阅 数据库文件和文件组For more information, see Database Files and Filegroups.

NAME = new_filegroup_name 将文件组名称改为 new_filegroup_name 。NAME = new_filegroup_name Changes the filegroup name to the new_filegroup_name.

AUTOGROW_SINGLE_FILEAUTOGROW_SINGLE_FILE

当文件组中的某个文件达到自动增长阈值时,只有该文件会增长。When a file in the filegroup meets the autogrow threshold, only that file grows. 这是默认值。This is the default.

AUTOGROW_ALL_FILESAUTOGROW_ALL_FILES

当文件组中的某个文件达到自动增长阈值时,文件组中的所有文件都会增长。When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.

<filegroup_updatability_option>::= filegroup_updatability_option>

对文件组设置只读或读/写属性。Sets the read-only or read/write property to the filegroup.

READ_ONLY | READONLY 指定文件组为只读。READ_ONLY | READONLY Specifies the filegroup is read-only. 不允许更新其中的对象。Updates to objects in it are not allowed. 主文件组不能设置为只读。The primary filegroup cannot be made read-only. 若要更改此状态,您必须对数据库有独占访问权限。To change this state, you must have exclusive access to the database. 有关详细信息,请参阅 SINGLE_USER 子句。For more information, see the SINGLE_USER clause.

因为只读数据库不允许数据修改,所以将发生以下情况:Because a read-only database does not allow data modifications:

  • 系统启动时,将跳过自动恢复。Automatic recovery is skipped at system startup.
  • 不能收缩数据库。Shrinking the database is not possible.
  • 在只读数据库中不会进行锁定。No locking occurs in read-only databases. 这可以加快查询速度。This can cause faster query performance.

备注

MicrosoftMicrosoftSQL ServerSQL Server 的未来版本中,将删除 READONLY 关键字。The keyword READONLY will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新的开发工作中使用 READONLY,并计划修改当前使用 READONLY 的应用程序。Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. Use READ_ONLY instead.Use READ_ONLY instead.

READ_WRITE | READWRITE 指定组是 READ_WRITE。READ_WRITE | READWRITE Specifies the group is READ_WRITE. 允许更新文件组中的对象。Updates are enabled for the objects in the filegroup. 若要更改此状态,您必须对数据库有独占访问权限。To change this state, you must have exclusive access to the database. 有关详细信息,请参阅 SINGLE_USER 子句。For more information, see the SINGLE_USER clause.

备注

MicrosoftMicrosoftSQL ServerSQL Server 的未来版本中,将删除 READWRITE 关键字。The keyword READWRITE will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新的开发工作中使用 READWRITE,并计划将当前使用 READWRITE 的应用程序修改为使用 READ_WRITEAvoid using READWRITE in new development work, and plan to modify applications that currently use READWRITE to use READ_WRITE instead.

可通过查看 sys.databases 目录视图中的is_read_only 列,或者查看 DATABASEPROPERTYEX 函数的 Updateability 属性,来确定这些选项的状态。The status of these options can be determined by examining the is_read_only column in the sys.databases catalog view or the Updateability property of the DATABASEPROPERTYEX function.

备注Remarks

若要减小数据库的大小,请使用 DBCC SHRINKDATABASETo decrease the size of a database, use DBCC SHRINKDATABASE.

BACKUP 语句正在运行时,不能添加或删除文件。You cannot add or remove a file while a BACKUP statement is running.

可以为每个数据库指定最多 32,767 个文件和 32,767 个文件组。A maximum of 32,767 files and 32,767 filegroups can be specified for each database.

示例Examples

A.A. 向数据库中添加文件Adding a file to a database

以下示例将一个 5 MB 的数据文件添加到 AdventureWorks2012AdventureWorks2012 数据库。The following example adds a 5-MB data file to the AdventureWorks2012AdventureWorks2012 database.

USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
  NAME = Test1dat2,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B.B. 向数据库中添加由两个文件组成的文件组Adding a filegroup with two files to a database

以下示例在 AdventureWorks2012AdventureWorks2012 数据库中创建文件组 Test1FG1,然后将两个 5 MB 的文件添加到该文件组。The following example creates the filegroup Test1FG1 in the AdventureWorks2012AdventureWorks2012 database and adds two 5-MB files to the filegroup.

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = test1dat3,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

C.C. 从数据库中删除文件Removing a file from a database

以下示例删除示例 B 中添加的一个文件。The following example removes one of the files added in example B.

USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO

D.D. 修改文件Modifying a file

以下示例增加示例 B 中添加的一个文件的大小。带有 MODIFY FILE 命令的 ALTER DATABASE 只能增大文件大小,因此,如果需要缩小文件大小,则需使用 DBCC SHRINKFILE。The following example increases the size of one of the files added in example B. The ALTER DATABASE with MODIFY FILE command can only make a file size bigger, so if you need to make the file size smaller you need to use DBCC SHRINKFILE.

USE master;
GO

ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

此示例将数据文件的大小缩小为 100 MB,然后指定该数量的大小。This example shrinks the size of a data file to 100 MB, and then specifies the size at that amount.

USE AdventureWorks2012;
GO

DBCC SHRINKFILE (AdventureWorks2012_data, 100);
GO

USE master;
GO

ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

E.E. 使文件组成为默认文件组Making a filegroup the default

以下示例使示例 B 中创建的 Test1FG1 文件组成为默认文件组。The following example makes the Test1FG1 filegroup created in example B the default filegroup. 然后,默认文件组被重置为 PRIMARY 文件组。Then, the default filegroup is reset to the PRIMARY filegroup. 请注意,必须使用括号或引号分隔 PRIMARYNote that PRIMARY must be delimited by brackets or quotation marks.

USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

F.F. 使用 ALTER DATABASE 添加文件组Adding a Filegroup Using ALTER DATABASE

以下示例将 FILEGROUP 添加到 MyDB 数据库。The following example adds a FILEGROUP to the MyDB database.

--Create and add a FILEGROUP
ALTER DATABASE MyDB
ADD FILEGROUP NewFG;
GO

--Add a file to FILEGROUP
ALTER DATABASE MyDB
ADD FILE
(
    NAME= 'MyFile',
)
TO FILEGROUP NewFG;
GO

G.G. 更改文件组,以便当文件组中的某个文件达到自动增长阈值时,文件组中的所有文件都会增长Change filegroup so that when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow

以下示例会生成所需的 ALTER DATABASE 语句,以使用 AUTOGROW_ALL_FILES 设置修改读写文件组。The following example generates the required ALTER DATABASE statements to modify read-write filegroups with the AUTOGROW_ALL_FILES setting.

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements
--so that all read-write filegroups grow at the same time.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, isdone bit);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id int IDENTITY(1,1), [dbid] int, [dbname] sysname, fgname sysname, isdone bit);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid int, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

    SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
    INSERT INTO #tmpfgs
    EXEC (@query)

    UPDATE #tmpdbs
    SET isdone = 1
    WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
    WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
    BEGIN
        SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

        SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

        PRINT @query

        UPDATE #tmpfgs
        SET isdone = 1
        WHERE [dbid] = @dbid AND fgname = @fgname
    END
END;
GO

另请参阅See Also