Move Data From one file to another file in same filegroup in SQL Server

Problem:-

We need to delete the second data file (.ndf) from the database without deleting the data itself.in another saying merge or move data from second data file(.ndf) to Primary data file(.mdf).

Solution:-

Using DBCC shrink with parameter empty file. (very dangerous action backup needed first)

Notes:-

Separation data files is a good practice some time !! as a performance enhancement (better I/O) but not usually as Paul Randal explains @ that reference. But sometimes that action needed to use in critical space problems.

Code I used

DBCC SHRINKFILE (Data_Hub_T24_2020_log_3, EMPTYFILE);  

GO;

how To in Steps:-

DBCC SHRINKFILE (ndf_logical_name, EMPTYFILE); GO;

 

DBCC shrink file

2. Try to remove ndf file.

Trying to remove .ndf file after DBCC

happy Troubleshooting 👍

Ref:-

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15&viewFallbackFrom=sql-server-ver13