Follow the Simple article on how to Add file and filegroup SQL server by our Microsoft SQL Server Support Services. Bobcares offers solutions to every query that comes our way.

Using SQL Server to add files and filegroup to a database

Access to Object Explorer. From the SQL Server Database Engine Connect to an instance and next expand that Database instance.

Now Right-click the database to add the files. Further, click Properties.

In the Database Properties dialog box. Select the Files page.

Click Add To update a data or transaction log file.

Enter a logical name for the file in the Database files grid. The file name should be unique within the database.

Select the file type, data, or log.

For a data file, select the filegroup in which the file should be included from the list or select <new filegroup> to create a new filegroup. Note that Transaction logs cannot be put in filegroups.

Specify the initial size of the file. Make the data file as large as possible that is based on the maximum amount of data.

To specify how the file should grow. Click (…) in the Autogrowth column. Select from the following options:

• To allow the selected file to grow more data space as required. Select the check box of Enable Autogrowth.

• To specify, the file should grow by fixed increments. Choose In Megabytes and also specify a value.

• The file will grow by a percentage of the current file size. Select In Percent value with the specification.

To specify the maximum file size limit. Select from the following options:

• Specify the maximum size file. Select Restricted File Growth size MB and specify a value.

• Allow for the file to grow maximum as required. Select Unrestricted File Growth.

• To prevent the file from growing, you need to select clear the Enable Autogrowth check box. However, the size mentioned in the file will not grow beyond the value specified in the Initial Size MB column.

Specify the path for the file location and click OK.

Using Transact-SQL

To add data or log files to the database

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the below example into the query window and click Execute option. In this example, it adds a filegroup with two files to a database. The example creates the filegroup Test1FG1 in the AdventureWorks20 database and adds two 5-MB files to the filegroup.

SQL

USE master
GO
ALTER DATABASE AdventureWorks20
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks20 
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

Following this, the SQL Server database will have two operating system files. i.e: the data file, and the log file.

The data file will hold the actual data in the database. As well as the objects like indexes, tables, views, and stored procedures. The log file contains the information required to recover all transactions in the database.

When a database is created with the default options, then both of these files will create. These files will be created in the primary filegroup. This filegroup contains the primary data file and any secondary files will not be put into other filegroups. Hence the primary filegroup is the default filegroup.

Similarly, when you create multiple data files. You will have the option of grouping into a user-defined filegroup. This allows to group files logically within their own filegroup that was created. Accordingly, the Database objects will remain within the primary filegroup.

View the Filegroups

The filegroups can be viewed for a given database by querying the sys.filegroups catalog view.

Example:

USE Solutions;
SELECT name, type_desc
FROM sys.filegroups;

Result:

name                  type_desc     
--------------------  --------------
PRIMARY               ROWS_FILEGROUP
Solutions1Filegroup1  ROWS_FILEGROUP

You can even use an asterisk (*) key to return all rows if needed.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, This article shows how to create a user-defined filegroup and add some data files to it.