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.

選取產品Select a product

在以下資料列中,選取您感興趣的產品名稱,隨即只會顯示該產品的資訊。In the following row, select the product name you're interested in, and only that product’s information is displayed.

* SQL Server *  * SQL Server *  

 

語法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_nameSQL 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 SQL ServerSQL Server 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. 如需詳細資訊,請參閱 SQL Server 備份及還原與 Microsoft Azure Blob 儲存體服務For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. 適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本)。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) 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>::=<filespec>::=

控制檔案屬性。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 SQL ServerSQL Server 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 路徑,則在您執行 ALTER DATABASE 之前,C:\MyFiles 必須存在;但是 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 會參考將儲存記憶體最佳化資料的路徑。' memory_optimized_data_path ' 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 路徑,則在您執行 ALTER DATABASE 之前,C:\MyFiles 必須存在;但是 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 和 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.

size 未提供主要檔案的大小時,SQL 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 EngineDatabase Engine 會讓檔案的大小變成 1 MB。When a secondary data file or log file is specified but size is not specified for the file, the Database EngineDatabase 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. 若要指定 MB 的小數,請將數字乘以 1024,以便將值轉換成 KB。To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. 例如,請指定 1536 KB,而不要指定 1.5 MB (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 Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本)Applies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) 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 =new_filegroup_name } 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_nameDEFAULT Changes the default database filegroup to filegroup_name. 資料庫中只能有一個檔案群組是預設檔案群組。Only one filegroup in the database can be the default filegroup. 如需相關資訊,請參閱 Database Files and FilegroupsFor more information, see Database Files and Filegroups.

NAME = new_filegroup_name 將檔案群組名稱變更為 new_filegroup_nameNAME = new_filegroup_name Changes the filegroup name to the new_filegroup_name.

AUTOGROW_SINGLE_FILE 適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本)AUTOGROW_SINGLE_FILE Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) 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 Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本)Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) 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.

在未來的 READWRITEMicrosoftMicrosoft 版本中,將移除 SQL ServerSQL Server 關鍵字。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

依預設,資料檔和記錄檔初始化的方式是在您執行下列作業之一時,在檔案中填入 0: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

即使 FILESTREAM 容器已使用 "DBCC SHRINKFILE" 作業來清空,基於各種系統維護原因,資料庫可能仍然需要維護已刪除檔案的參考。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. 執行 ALTER DATABASE 搭配 REMOVE FILE 選項,移除這個容器。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

下列範例會將兩個 5 MB 的記錄檔加入 AdventureWorks2012AdventureWorks2012 資料庫中。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 範例中所新增的其中一個檔案大小。ALTER DATABASE 搭配 MODIFY FILE 命令只能讓檔案大小更大,若您需要讓檔案大小更小,則必須使用 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 Database
受控執行個體 *
* SQL Database
Managed Instance *

 

 

Azure SQL 受控執行個體Azure SQL Managed Instance

將此陳述式與 Azure SQL 受控執行個體中的資料庫搭配使用。Use this statement with a database in Azure SQL Managed Instance.

Azure SQL 受控執行個體的語法Syntax for Azure SQL 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_nameSQL 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 SQL ServerSQL Server 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>::=<filespec>::=

控制檔案屬性。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 SQL ServerSQL Server 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.

size 未提供主要檔案的大小時,SQL 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 EngineDatabase Engine 會讓檔案的大小變成 1 MB。When a secondary data file or log file is specified but size is not specified for the file, the Database EngineDatabase 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. 若要指定 MB 的小數,請將數字乘以 1024,以便將值轉換成 KB。To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. 例如,請指定 1536 KB,而不要指定 1.5 MB (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 =new_filegroup_name } 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_nameDEFAULT Changes the default database filegroup to filegroup_name. 資料庫中只能有一個檔案群組是預設檔案群組。Only one filegroup in the database can be the default filegroup. 如需相關資訊,請參閱 Database Files and FilegroupsFor more information, see Database Files and Filegroups.

NAME = new_filegroup_name 將檔案群組名稱變更為 new_filegroup_nameNAME = 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. 請改用 READ_ONLY。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 範例中所新增的其中一個檔案大小。ALTER DATABASE 搭配 MODIFY FILE 命令只能讓檔案大小更大,若您需要讓檔案大小更小,則必須使用 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