Autogrow. Primary vs additional data files

Question

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

Answer 1

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

Answer 2

But I see nothing that tells it whether to grow the primary MDF, or grow by adding additional NDFs.

There is NO option native in SQL Server that will allow you to do this, unless you explicitly specify to add additional secondary files (.ndf) using Alter database command.

More info Files and Filegroups Architecture & Database Files and Filegroups.

Is there a way to combine these NDF files back into the primary MDF?

Yes using DBCC SHRINKFILE ('ndf-secondary-file-name', EMPTYFILE)

Note: You can move data from one FILEGROUP to another FILEGROUP. But if it is same filegroup, then SQL Server will manage where the data goes when you have multiple files in the same filegroup using PROPORTIONAL FILL algorithm (i.e. allocating the extent from the file that has the most free space).

Now when you have secondary file belonging to different filegroup, then what ?

Now to move a table with a cluster index to a new file group, use ALTER TABLE and specify the file group name that should host the cluster index. Then issue a REBUILD INDEX and the rebuilt index will be created in the new file group. To move a table without a cluster index, the simplest option is to define a cluster index on the table for the duration of the move and then drop it after the move is complete.

If you are curious as to what tables and Indexes belong to what file group and their physical location, then use below query :

select distinct
        idx.name as IndexName
        ,object_name(idx.id) as TableName
        ,fil.name as FileGroup
        ,dbf.physical_name as PhysicalFileName
    from sys.tables tb
         inner join sys.sysindexes idx
         on idx.id = tb.object_id 
         and tb.type = 'U' and idx.name is not null
         inner join sys.filegroups fil
         on fil.data_space_id = idx.groupid
         inner join sys.database_files dbf
         on fil.data_space_id = dbf.data_space_id
         inner join sys.data_spaces dsp
         on fil.data_space_id = dsp.data_space_id