In SQL Server, additional files can be added to a filegroup configured with fixed-size data files to allow more data to be stored in the database. This can be useful when the existing data files are approaching their maximum capacity. In this article, we will explore how SQL Server spreads data across multiple files and how to add a new data file to a filegroup.

Setting up the Database

Let’s start by creating a new database with a fixed-size data file. We will use AdventureWorks2014 as our sample database. Here is the T-SQL code to set up the database:

CREATE DATABASE [AdventureWorks2014_Expand]
ON PRIMARY 
( NAME = N'AdventureWorks2014_Expand', FILENAME = N'C:\Program Files\Microsoft SQL Server\AdventureWorks2014_Expand_Fixed1.mdf' , SIZE = 15360KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0)
LOG ON 
( NAME = N'AdventureWorks2014_Expand_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\AdventureWorks2014_Expand_log.ldf' , SIZE = 10176KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Once the database is created, we can proceed to add a sample table and populate it with data from AdventureWorks2014:

USE [AdventureWorks2014_Expand]
GO
CREATE TABLE [dbo].[PersonPhone](
[BusinessEntityID] [int] NOT NULL,
[PhoneNumber] [nvarchar](25) NOT NULL,
[PhoneNumberTypeID] [int] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO PersonPhone (
BusinessEntityID
, PhoneNumber
, PhoneNumberTypeID
, ModifiedDate)
SELECT * 
FROM AdventureWorks2014.Person.PersonPhone
GO

Monitoring Data File Usage

Now that we have our database set up, let’s monitor the usage of the data file. We can use the sys.master_files system table to get information about the capacity and current size of the data file:

SELECT
  db_name(database_id) as databaseName,
  name AS FixedFileName,
  physical_name AS FixedFilePath,
  [file_id] AS fileID,
  (size * 8.0)/1024 AS FileSize,
  FILEPROPERTY(name, 'spaceused') *8/1024 AS [UsedSpace],
 (FILEPROPERTY(name, 'spaceused') *8/1024)/((size * 8.0)/1024) AS PercentUsed
FROM sys.master_files 
WHERE database_id = db_id()

This query will give us the current size and percentage of space used for the data file. If the data file is approaching its maximum capacity, we may need to add a new data file to accommodate more data.

Adding a Second Data File

To add a new data file to the filegroup, we can use SQL Server Management Studio (SSMS) or T-SQL. In SSMS, right-click on the database, select Properties, and go to the Files page. Click on Add to add a new data file with the desired size. Here is the T-SQL code to add a new data file:

ALTER DATABASE [AdventureWorks2014_Expand] 
ADD FILE ( NAME = N'AdventureWorks2014_Expand_Fixed2'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\AdventureWorks2014_Expand_Fixed2.ndf' 
, SIZE = 10240KB 
, FILEGROWTH = 0) 
TO FILEGROUP [PRIMARY]
GO

Once the new data file is added, SQL Server will distribute the data across both files, allowing for more data insertions.

Observing Proportional Fill

To observe how SQL Server spreads data across multiple data files, we can set up an Extended Events session. This session will capture information about the database inserts and show us how SQL Server allocates writes to different data files.

Here is how to set up the Extended Events session:

1. Create a new Extended Events session through the Management folder in SSMS.
2. Select the event to capture, in this case, physical_page_write.
3. Configure the output of the session to capture the event data.
4. Start the session and run the insert script to see the captured data.

By analyzing the captured data, we can see how SQL Server distributes writes between the existing and new data files.

Conclusion

In this article, we explored the concept of filegroups and data files in SQL Server. We learned how to add a new data file to a filegroup to accommodate more data and how to monitor data file usage. We also saw how to set up an Extended Events session to observe how SQL Server spreads data across multiple data files. Understanding these concepts can help optimize database performance and manage data growth effectively.