How can I make data fall in newly created ndf file in SQL Server

Question

I have a very big database and its .MDF and .LDF files are stored on different drives (say drive D and E); now the D drive which has the .MDF file is running out of space so I created a new .NDF file on another new drive (say drive H), but after I run some insert/update scripts, it still shows there is no space on the D drive. How do I make the newly created .NDF to make use of modified data to get allocate in it?

Possible duplicate of [Efficient way to change the table's filegroup](https://stackoverflow.com/questions/40928315/efficient-way-to-change-the-tables-filegroup) — Dale K, Mar 15 '19 at 05:04

score 2 · Answer 1 · edited Mar 15 '19 at 05:49

2

Make the newly created .ndf file as default data file. Like below :

USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

edited Mar 15 '19 at 05:49

Firdous nath

1,487
1
14
38

answered Mar 15 '19 at 05:10

Shashi Kumar Singh

36
1

I made the recently created .NDF file as primary filegroup and as a default. I'm trying to add a new field of INT datatype with NULL via TSQL designer, its been since half an hour and not yet saved eventually SQL server raise error as NOT Responding . Any advice please ? – Mar1009 Mar 15 '19 at 06:12

Alexander Volok · Answer 2 · 2019-03-15T09:33:50.800

It can be achieved by setting the MAXSIZE proprerty of the MDF file equal to current size:

ALTER DATABASE [yourDB] MODIFY FILE ( NAME = N'yourDB_mdf', SIZE = 500MB , MAXSIZE = 500MB )

In this case, all new data pages will be allocated and written to a new NDF. The second data file, of course, should have no MAXSIZE restrictions.

However, there is no easy way to rebalance existing data. One of the approaches is:

create a new FILEGROUP with multiple files
rebuild indexes using CREATE INDEX … WITH (DROP_EXISTING=ON) plus specifying the new filegroup
Shrink or delete the old filegroup to reclaim disk space.

Another approach is: Split a file group into multiple data files

How can I make data fall in newly created ndf file in SQL Server

2 Answers2