Migrate Data from mdf file to ndf File in Same File Group

This blog discusses the various steps that can be taken to migrate data from an MDF file to an NDF file within the same file group.

Problem: A Database integrity job was failing due to IOPS issue for TestDB database which is more than 2 TB in size. Due to large file size, it was becoming difficult to manage the database.

Approach: To troubleshoot this, we decided to split the data between 2 data files. So, the current state of the drive space and data file is as below:

Our data file is hosted in N:\ drive and we will be creating another file in the same location. Our approach is to start the data transfer by using the emptyfile command and the manually stop the query in between, in order to forcefully stop the data movement. 

NOTE: Manually stopping the query in between will not have any impact on the database (integrity/consistency). Then we will shrink the mdf file to reclaim the free space.

Solution: Follow the below steps to split the data between multiple SQL Server data files. 
First, we need to add a secondary data file in which we will be inserting out data. It will be added as ndf (next data file). Run the script below to add additional data files on TestDB database

USE [master]
GO
ALTER DATABASE [TestDB] ADD FILE ( NAME = N'TestDB_1', FILENAME = 
N'N:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_1.ndf' , 
SIZE = 209715200KB , FILEGROWTH = 5242880KB ) TO FILEGROUP [PRIMARY]GO 

Once you execute this script, it will add a new data file named _TestDB_1_ in N:\ drive and the size will be 200 GB (We took this with context to our database). We have set the file growth of 5 GB and the datafile will be added to primary file group.
 
Now, after adding the data file start DBCC emptyfile operation on TestDB database. The syntax will be:

So, in our case it will be:

use YOURDATABASE
go

USE [TestDB]

go

DBCC shrinkfile ('TestDB’,emptyfile)

Here, TestDB is the logical name of the file from which we want to remove the data i.e our mdf file.
Now once we have started this operation, we need to keep track on how much of the data is moved from mdf to ndf. You can use the below query to keep the track of the same:

USE [TestDB]
GO
SELECT
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[USEDSPACE_%] = CAST((CAST(FILEPROPERTY(A.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(A.size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;

For our approach, we wanted the ndf to be at around 500 GB, so once the ndf reaches this size, we can stop the emptyfile operation.
Once the emptyfile operation is stopped, we need to manually reclaim the free space in mdf by using below query:
 

DBCC Shrinkfile('TestDB', 1500000) --  

We need to change the size in smaller chunks

Now our MDF was 2 TB, we moved 500 GB to NDF, hence 500 GB is reclaimable from MDF, which we just reclaimed using the above query.

We can repeat this step multiple times to move the data between datafiles, manually stopping the operation in between according to our storage and then reclaiming the space again.

NOTE: While using emptyfile on MDF you won’t be able to fully empty the contents of the primary data file with file ID 1. To get the file ID number, run this script.

select file_id, name,physical_name from sys.database_files

Here, in this example, the filename is “mo” and file_id is 1. When you try emptying the file mo which has file_id  1, you will encounter this error message.

 This is because there is system information within the original file, which cannot be emptied. But, if you try the same command on the other data file “mo2data”, the empty file command will succeed.

Conclusion

Once this data movement activity is complete, please run database maintenance jobs:

• Index optimize job
• Integrity check job
• Full database backup job.