Sql-server – Autogrow. Primary vs additional data files

datafilefilegroupssql server

My databases all use autogrow, which grows the primary MDF file on a percentage. But one of the databases, from a third party application grows by adding additional .NDF files.

Where is this option set? When I look at autogrow settings, there is the option to grow or not, by percentage or by xMB, and an option for limited or unlimit growth. But I see nothing that tells it whether to grow the primary MDF, or grow by adding additional NDFs.

And, is there a way to combine these NDF files back into the primary MDF?

Thanks!

RZ

Best Answer

Like @JonSeigel said above in his comment, this is not a SQL Server action. Your third party application(s) must be doing this on their own, or somebody manually creating the additional data files.

And, is there a way to combine these NDF files back into the primary MDF?

What you're looking for is DBCC SHRINKFILE(YourDataFileName, EMPTYFILE); where YourDataFileName is the logical name of the *.ndf file(s). What this does is, provided you have sufficient space in the other data file(s) in the containing filegroup, is it empties the database data file by moving all of the data into the other data files in the same filegroup. You can then remove it.

See this BOL reference: DBCC SHRINKFILE

There is an example on that same page that I will quote verbatim (as it seems to be exactly what you are trying to do [Note: The below excerpt is taken from the above link]):

D. Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.

USE AdventureWorks2012; 
GO

-- Create a data file and assume it contains data. 
ALTER DATABASE AdventureWorks2012  
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    ); 
GO

-- Empty the data file. 
DBCC SHRINKFILE (Test1data, EMPTYFILE); 
GO

-- Remove the data file from the database. 
ALTER DATABASE AdventureWorks2012 
REMOVE FILE Test1data; 
GO
Related Question