What is the use of creating .ndf files for a SQL Server Database?

saratcm
saratcm used Ask the Experts™
on
Hi,
   I have a prod database which has too many datafiles and some secondary file(.ndf).
Can some one tell me the use of .ndf files?

If we have multiple datafiles(5) under same file group for same database, Can we make them into single using any process.
I think we can do it by taking backup of that database and restoring.
But is there any way I can get rid of all those multiple(.mdf) files and make them into single file, since it is a prod databse and I can't take it offline to drop it and restore.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can take the shrink & migrate approach, as long as they all belong to the same filegroup.
DBCC SHRINK FILE with EMPTYFILE option.
Read Books Online for more details.
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.

DBCC SHRINKFILE 
(
     { 'file_name' | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

Open in new window

Some more from BOL:

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 AdventureWorks;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks 
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 AdventureWorks
REMOVE FILE Test1data;
GO

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Regarding WHY to use multiple data files. Simply put, in some instances, you might want to split the physical layout of your data across multiple spindles. Back in the day, with local storage, if you had multiple disk arrays, you could split your data files and force objects on each one by using different filegroups. You could avoid excessive disk load by specifying where each object was to be maintained. In other instances, you could use a secondary file to migrate the bulk of your data off to another drive if you were running low on storage. Particularly usefuly for poor planned implementations -or rushed jobs.

Author

Commented:
Thanks for your commands,
Can we achieve that by taking the full backup of that database and restoring it freshly on that server with the same name.
Bcoz the above process looks for me like little bit complex.

Coming to the 4th command u made, I mean we have more data files on same file group, not on different file groups. Some files are <5Mb and some 20GB like that.

Can you tell me some thing more abt secondary file(.ndf)?  I am not having clear idea on that
The the following command. It will give me most of the information I need to create the actual scripts for you.

SP_HELPDB '[DBNAME]'

Open in new window

And no, you won't be able to eliminate data files through backup restores.

Author

Commented:
Hi,
   below is my database info

name                                                                                                                             fileid filename                                                                                                                                                                                                                                                         filegroup                                                                                                                        size               maxsize            growth             usage    
db_name0eData                                                                                                                  1      E:\MSSQL\Data\db_name0e.mdf                                                                                                                                                                                                                                    PRIMARY                                                                                                                          101987328 KB       Unlimited          10%                data only
db_namelog0e                                                                                                                   2      E:\MSSQL\Data\db_namelog0e.ldf                                                                                                                                                                                                                                 NULL                                                                                                                             614400 KB          Unlimited          0%                 log only
db_name1eData                                                                                                                  3      E:\MSSQL\Data\db_name1e.ndf                                                                                                                                                                                                                                    PRIMARY                                                                                                                          5120 KB            7251520 KB         10%                data only
db_name2eData                                                                                                                  4      E:\MSSQL\Data\db_name2e.ndf                                                                                                                                                                                                                                    PRIMARY                                                                                                                          5120 KB            7251520 KB         0%                 data only
db_name3eData                                                                                                                  5      E:\MSSQL\Data\db_name3e.ndf                                                                                                                                                                                                                                    PRIMARY                                                                                                                          5120 KB            7251520 KB         0%                 data only
db_name4eData                                                                                                                  6      E:\MSSQL\Data\db_name4e.ndf                                                                                                                                                                                                                                    PRIMARY                                                                                                                          5120 KB            7251520 KB         0%                 data only
db_name5eData                                                                                                                  7      E:\MSSQL\Data\db_name5e.ndf                                                                                                                                                                                                                                    PRIMARY                                                                                                                          5120 KB            7251520 KB         0%                 data only
db_name6eData                                                                                                                  8      E:\MSSQL\Data\db_name6e.ndf                                                                                                                                                                                                                                    PRIMARY                                                                                                                          5120 KB            7251520 KB         0%                 data only
db_name_data1                                                                                                                  9      E:\MSSQL\Data\db_name_data1.ndf                                                                                                                                                                                                                                PRIMARY                                                                                                                          13633536 KB        Unlimited          10%                data only
db_name_log1                                                                                                                   10     E:\MSSQL\Data\db_name_log1.ldf                                                                                                                                                                                                                                 NULL                                                                                                                             614400 KB          Unlimited          0%                 log only

Author

Commented:
what is the use of (.ndf) files. What is the benefit of creating a .ndf file instead of creating a .mdf file.
and can you tell me the difference b/w them too?
.mdf is reserved for primary data files; .ndf is used generally for secondary data files. This is only recommended nomenclature, as you can decide what extension to use for them.
I'll try to get you the file migration scripts this evening.
Start reviewing this. Its pretty repetitive: you migrate off from one file, empty it, the you remove it from your DB make up, move on to the next file & repeat.

If you want to speed things up: ensure that the .mdf file where you want to consolidate everything can grow. Set the other files (.ndf) to not auto-grow. That will force the data to migrate off to the only data file that can accommodate the contents (i.e. your sole .mdf file).

Now, your data files are quite large, make sure that you take one backup before starting this and test it with a single file first. This is going to be one lengthy process for you.

Good luck!

use [YouDatabaseName]
go
dbcc shrinkfile (db_name1eData,EMPTYFILE)
GO
ALTER DATABASE [YourDatabaseName]
REMOVE FILE db_name1eData
GO
dbcc shrinkfile (db_name2eData,EMPTYFILE)
GO
ALTER DATABASE [YourDatabaseName]
REMOVE FILE db_name2eData
GO
dbcc shrinkfile (db_name3eData,EMPTYFILE)
GO
ALTER DATABASE [YourDatabaseName]
REMOVE FILE db_name3eData
GO
dbcc shrinkfile (db_name4eData,EMPTYFILE)
GO
ALTER DATABASE [YourDatabaseName]
REMOVE FILE db_name4eData
GO
dbcc shrinkfile (db_name5eData,EMPTYFILE)
GO
ALTER DATABASE [YourDatabaseName]
REMOVE FILE db_name5eData
GO
dbcc shrinkfile (db_name6eData,EMPTYFILE)
GO
ALTER DATABASE [YourDatabaseName]
REMOVE FILE db_name6eData
GO
dbcc shrinkfile (db_name7eData,EMPTYFILE)
GO
ALTER DATABASE [YourDatabaseName]
REMOVE FILE db_name7eData
GO

Open in new window

Author

Commented:
Thankful to you for your help and have a small quest, will it effect any thing on my database? Since it is a production server.
It will have an impact on response time, and can fail misserably if you are emptying a file which is actively changing due to data manipulation. I suggest you do this, in chunks, during off-peak hours or during a maintenance window.

Author

Commented:
Good

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial