Alter database modify file filegroup

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.

Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Linked 1. Quick access. Search related threads. Remove From My Forums.

Answered by:. Archived Forums. Sign in to vote. Any thoughts would be much appreciated. Wednesday, January 21, PM. This scenario is easily reproduced, and seems not to be "by design".

I put my bets on Sean and say "by design". Friday, January 23, PM. What happens if you set the database offline and then online again? The following script creates a database and then adds additional filegroups. Although additional filegroups can be created when creating a database, I added the filegroups later for simplicity and illustration. So in this case, the primary filegroup will be the default filegroup.

At this point we have three file groups primary , FG1 and FG2 in our new database. Any new table or index will be created in the primary filegroup which is the default unless we specify the filegroup to use.

If we want to create a data storage object in FG1 or FG2 then the only way is to specify the filegroup when creating the storage object. Consider the following scenario where we want to create a table without specifying the filegroup.

It is essential to provide explicit instructions for creating the data storage objects in other than the default filegroup. The only way to ensure the creation of new data storage objects on FG1 or FG2 without any additional consideration is to set the required filegroup as shown below. Another issue we have since we do not have a file associated with this filegroup is if we try to change FG1 to be the default filegroup as shown below:.

We will get this error message, because a file group cannot be set as the default unless it has at least one data file. The file cannot be removed unless it is empty. If SIZE is specified, the new size must be larger than the current file size. For example:. Do not specify the operating-system file name. For more information, see Move Database Files. 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.

The name must be unique within the database and comply with the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier. The file must reside on the server on which SQL Server is installed. 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.

Only one file can be put on each raw partition. The path up to the last folder must exist, and the last folder must not exist. For more information on memory-optimized filegroups, see The Memory Optimized Filegroup. When size is not supplied for the primary file, the SQL Server 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 default is MB.

Specify a whole number and do not include a decimal.