ALTER DATABASE File and Filegroup Options (Transact-SQL)
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. For other ALTER DATABASE options, see ALTER DATABASE (Transact-SQL).
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' } ] [ , 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 ] | REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME = new_filegroup_name } } <filegroup_updatability_option>::= { { READONLY | READWRITE } | { READ_ONLY | READ_WRITE } }
<add_or_modify_files>::=
Specifies the file to be added, removed, or modified.
![]() |
---|
<add_or_modify_files> options are not available in a Contained Database. |
<filespec>::=
Controls the file properties.
![]() |
---|
<filespec> options are not available in a Contained Database. |
<add_or_modify_filegroups>::=
Add, modify, or remove a filegroup from the database.
<filegroup_updatability_option>::=
Sets the read-only or read/write property to the filegroup.
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.
To decrease the size of a database, use DBCC SHRINKDATABASE.
You cannot add or remove a file while a BACKUP statement is running.
A maximum of 32,767 files and 32,767 filegroups can be specified for each database.
In SQL Server 2005 or later, 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. If a filegroup is offline, any try to access the filegroup by an SQL statement will fail with an error. 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. However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup will fail.
Moving Files
In SQL Server 2005 or later, 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.
Removing a FILESTREAM Container
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 (Transact-SQL) will run the FILESTREAM Garbage Collector to remove these files when it is safe to do so. Unless the FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the ALTER DATABASEREMOVE FILE operation will fail to remove a FILESTREAM container and will return an error. The following process is recommended to remove a FILESTREAM container.
-
Run DBCC SHRINKFILE (Transact-SQL) with the EMPTYFILE option to move the active contents of this container to other containers.
-
Ensure that Log backups have been taken, in the FULL or BULK_LOGGED recovery model.
-
Ensure that the replication log reader job has been run, if relevant.
-
Run sp_filestream_force_garbage_collection (Transact-SQL) to force the garbage collector to delete any files that are no longer needed in this container.
-
Execute ALTER DATABASE with the REMOVE FILE option to remove this container.
-
Repeat steps 2 through 4 once more to complete the garbage collection.
-
Use ALTER Database...REMOVE FILE to remove this container.
A. Adding a file to a database
The following example adds a 5-MB data file to the AdventureWorks2012 database.
USE master; GO ALTER DATABASE AdventureWorks2012 ADD FILE ( NAME = Test1dat2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ); GO
B. Adding a filegroup with two files to a database
The following example creates the filegroup Test1FG1 in the AdventureWorks2012 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\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ), ( NAME = test1dat4, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP Test1FG1; GO
C. Adding two log files to a database
The following example adds two 5-MB log files to the AdventureWorks2012 database.
USE master; GO ALTER DATABASE AdventureWorks2012 ADD LOG FILE ( NAME = test1log2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.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. Removing a file from a database
The following example removes one of the files added in example B.
USE master; GO ALTER DATABASE AdventureWorks2012 REMOVE FILE test1dat4; GO
E. Modifying a file
The following example increases the size of one of the files added in example B.
USE master; GO ALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = test1dat3, SIZE = 20MB); GO
F. Moving a file to a new location
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. Afterward, stop and start the instance of SQL Server or take the AdventureWorks2012 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. Moving tempdb to a new location
The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.
-
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
-
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
-
Stop and restart the instance of SQL Server.
-
Verify the file change.
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');
-
Delete the tempdb.mdf and templog.ldf files from their original location.
H. Making a filegroup the default
The following example makes the Test1FG1 filegroup created in example B the default filegroup. Then, the default filegroup is reset to the PRIMARY filegroup. Note 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. Adding a Filegroup Using ALTER DATABASE
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 to --the FileStreamPhotoDB database. 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