Adding and Deleting Data and Transaction Log Files
Data and transaction log files can be added to expand a database or can be deleted to reduce the number of files in a database.
Important
SQL Server data and transaction log files must not be placed on compressed file systems. However, read-only secondary filegroups and read-only databases can be placed on a file system using NTFS file compression. For more information, see Read-Only Filegroups and Compression.
SQL Server uses a proportional fill strategy across all the files within each filegroup and writes an amount of data proportional to the free space in the file. This enables the new file to be used immediately. In this way, all files generally become full at about the same time. However, transaction log files cannot be part of a filegroup; they are separate from one another. As the transaction log grows, the first log file fills, then the second, and so on, by using a fill-and-go strategy instead of a proportional fill strategy. Therefore, when a log file is added, it cannot be used by the transaction log until the other files have been filled first.
Adding Files
When a file is added, the file is available immediately for use by the database. When you add files to the database, you can specify the size of the file. When a size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. 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. The size specified for the primary file must be at least as large as the primary file of the model database. For more information, see model Database.
You can set the maximum size to which the file should grow if space within the file is exhausted. You can also set the increment by which the file should grow when that is required. If a maximum size is not specified, the file grows until the disk is full. If a file growth increment is not specified, the default value is 1 MB for data files and 10 percent for log files. The minimum value is 64 KB.
You can specify the filegroup to which the file belongs. Filegroups are named collections of files and are used to simplify data placement and administrative tasks such as backup and restore operations. For more information, see Using Files and Filegroups.
Deleting Files
Deleting a data or transaction log file removes the file from the database. You cannot remove a file from the database unless the file has no existing data or transaction log information; the file must be completely empty before it can be removed. To empty a data file by moving data from the data file to other files in the same filegroup, use the DBCC SHRINKFILE statement and specify the EMPTYFILE clause. Because the Database Engine no longer allows data to be placed on the file after the shrink operation, the empty file can be removed by using the ALTER DATABASE statement or SQL Server Management Studio.
You cannot move transaction log data from one log file to another to empty a transaction log file. To remove inactive transactions from a transaction log file, the transaction log must be truncated or backed up. When the transaction log file no longer contains any active or inactive transactions, the log file can be removed from the database. For more information, see Managing the Transaction Log.
Important
After you add or delete files, create a database backup immediately. A transaction log backup should not be created until after a full database backup is created.
To add or delete data or log files to a database
See Also