The SQL Server databases work with multiple files, such as MDF, NDF, and LDF files. As SQL Server database files grow, it becomes difficult for users to manage data in their databases. In such cases, NDF files in SQL Server play an important role. These files allow database administrators to expand storage capacity and improve overall database performance. 

With the help of this blog, we will learn more about these secondary database files and know why they are important in SQL and how they work. 

What are NDF Files in SQL Server? Know the Secondary Data File in SQL

The secondary database file, or NDF file, is responsible for storing user data alongside the primary database file (MDF file). These secondary files are optional files, unlike the MDF files that are mandatory for each database. Here are some of the uses for NDF files:

  • When the primary database file grows large.
  • In case additional storage is required. 
  • When data is required to be distributed among different disks. 
  • For better storage management with large databases.

In these situations, the secondary database files help with managing and storing the data more efficiently in the SQL Server database. Now, as we read already, the NDF files are optional files; hence, users can create these files when they need them. We will now take a look at some situations where these files are needed. 

Why DBAs Create Secondary Database Files?

Here are some of the common situations that require database administrators to create the secondary database files.

  • To increase database storage in SQL Server
  • For improved performance of the database
  • Efficient filegroup management in SQL Server
  • For simplified storage management and better disk utilization

This is where the NDF files help users with storage efficiency and management. We will now take a look at the steps for creating NDF files in SQL Server. 

How to Create NDF File in SQL Server? All Steps Explained

Database administrators can create .ndf files as per their requirements with the help of SSMS and by following the given steps:

  1. Open SSMS and connect it to the SQL Server instance. 
  2. Next, expand the Databases option and right-click on the desired database. 
  3. Go to Properties and then open the Files page. 
  4. Click on the Add button and further add the following details:
  5. Click on OK. SQL Server will immediately create the new NDF file. 

These steps will help users with how to add an NDF file in SQL Server. Now, there are various reasons why users create multiple NDF files. A few other reasons include:

  • When the database size grows to more than hundreds of GBs.
  • Due to large tables needing dedicated storage spaces. 
  • When separate filegroups are created in SQL Server. 

These are some of the reasons why multiple NDF files are created. However, many situations also demand that database administrators either shrink these NDF files or delete them as well. We will now move to the reasons for shrinking or deleting these files and further find effective ways to delete NDF files in the database. 

Common Reasons to Shrink or Delete SQL Server NDF Files

  • Even after deleting a large volume of data and records, the NDF file might still have unused space. By shrinking the files, users can reclaim the space efficiently. 
  • Before transferring or migrating a database to another server, the database administrators might need to shrink the NDF files that are too large in size. 
  • After relocation of the database or records, when the NDF files are no longer required, users often delete them. 
  • In case an NDF file is created with the wrong size or filegroup, these secondary files are deleted and then recreated with the correct configuration. 

These are the common reasons for shrinking or deleting the SQL Server NDF files. We will now take a look at the methods that will help users with the deletion or shrinking of the files. 

How to Shrink or Delete NDF Files in SQL Server? Step-by-Step Explanation

The steps we will discuss will be performed using SQL Server Management Studio. We will now proceed with the process. 

Steps to Shrink SQL Server NDF Files

  1. Open SSMS and connect it to the SQL Server instance. 
  2. In Object Explorer, expand the Databases folder and choose the required database. 
  3. Then, right-click on the selected database and navigate to Tasks, choose Shrink, and then select the Files.
  4. In the displayed Shrink File Window, set File Type to Data and then select the NDF file you want to shrink from the drop-down menu. 
  5. Next, select a shrink action from the provided options:
    1. Release Unused Space: Helps free up unused space at the end of the file.
    2. Reorganize pages before Releasing Unused Space: Helps move the data pages to free the space.
  6. Then, specify the required file size.
  7. Lastly, click on the OK button to initiate the NDF file shrinking process. 

Now, moving on to the steps that will allow users to delete the secondary files in the SQL Server database. 

Steps to Delete NDF Files in SQL Server

Step 1: Open SSMS and connect it to the SQL Server instance. 

Step 2: Run the command given below to empty the file:

DBCC SHRINKFILE(‘File_Name’, EMPTYFILE);

Step 3: Next, in the Object Explorer, right-click on the desired Database and select the Properties option. 

Step 4: Then, go to the Files page and validate that the NDF file is empty. 

Step 5: Next, run the given command to remove the file from the database:

ALTER DATABASE Database_Name
REMOVE FILE File_Name;

Step 6: Validate the removed file with the following command:

SELECT name, physical_name
FROM sys.database_files;

Step 7: Once confirmed, delete the corresponding SQL Server NDF file from the disk. 

These steps will help users delete an NDF file in SQL Server with complete precision. However, it is important to run the command properly, as entering the incorrect database or file name can result in errors. There are times when users end up deleting the wrong database file due to incorrect names or other execution errors with the DELETE command. It is advised not to panic in such situations, and trust a dedicated solution to restore the deleted data. We will now take a look at a similar solution to help users with the same.

Deleted NDF File Accidentally? Here’s the Expert Solution

With the help of a specialized SQL Data Recovery Tool, like the one by SysTools, database administrators can effectively restore deleted NDF as well as MDF files with complete precision.

Download Now Purchase Now

This tool allows users to recover accidentally deleted data in SQL Server and further restore it to a desired destination, be it to a Live SQL Database or as SQL Script or CSV format. Let’s take a look at the steps of using this utility for data recovery.  

  1. Install and run the suggested software. Click on Open to browse for MDF and NDF files. run tool
  2. To recover deleted NDF files, browse the database files in the next window. The tool allows users to automatically detect the NDF files or manually select the files. add ndf files for recovery
  3. After adding the files, the tool will scan the files to proceed with the recovery process. scan database files
  4. Preview the provided restored data in the software panel and click on the Export Button.preview and click on export
  5. In the export window, select a destination to save restored data, and add database credentials or a destination path. add authentication details
  6. Then, select the database objects and click on the Export button. click on export

These steps will help users restore the accidentally deleted NDF files in SQL Server and further secure the data for future use. Here are some of the best practices that will help users keep their data safe while performing operations like adding, shrinking, creating, or deleting SQL NDF files.

Best Practices for Managing Secondary Database Files in SQL Server

  • It is suggested to add the NDF files only when necessary. 
  • For data safety and management, store the NDF files on separate physical drives. 
  • To organize data in a better way, use dedicated filegroups for assigning NDF files. This helps simplify database administration and restore strategies 
  • Appropriately setting autogrowth size can help with efficient growth management. Configuring small growth values for autogrowth can lead to file fragmentation.

These steps will help users monitor and maintain NDF files in the SQL database. With the help of these safety measures, database administrators can ensure the safety of their desired databases and database files. 

Conclusion

Through this technical guide, we have learned and discussed the NDF file in SQL Server. These are the secondary database files that allow users and database administrators to manage database storage efficiently. We have also discussed the methods and steps to create, add, shrink, or delete secondary database files in SQL Server. To ensure complete safety and data privacy, we have also addressed the best practices for NDF file.