Adding and Deleting Data and Transaction Log Files

Data and transaction log files can be added to expand a database or deleted to shrink a database. When a file is added, the file is available immediately for use by the database.

Important  Microsoft® SQL Server™ 2000 data and transaction log files must not be placed on compressed file systems.

SQL Server uses a proportional fill strategy across all the files within each filegroup, writing an amount of data proportional to the free space in the file and allowing the new file starts to be used immediately. This way all files tend to become full at about the same time. Transaction log files, however, 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, using a fill-and-go strategy rather than 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.

When adding files to the database, you can specify the size of the file (default is 1 MB), the maximum size to which the file should grow if space within the file is exhausted, the amount by which the file grows each time it needs to grow (default is 10 percent), and the filegroup to which the file belongs, as appropriate.

Deleting a data or transaction log file removes the file from the database. It is not possible to remove a file from the database unless there is no existing data or transaction log information on the file; the file must be completely empty before it can be removed. To migrate data from a data file to other files in the same filegroup, use the DBCC SHRINKFILE statement and specify the EMPTYFILE clause. SQL Server no longer allows data to be placed on the file, thereby allowing it to be deleted by using the ALTER DATABASE statement or the property page within SQL Server Enterprise Manager.

It is not possible to migrate the transaction log data from one log file to another to delete a transaction log file. To purge 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.

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 data or transaction log files to a database

Transact-SQL