Merging the MDF and NDF files of SQL Server databases

Tags:
MDF
NDF
SQL Server database
How can i merge the MDF and NDF files of SQL Server databases?

Answer Wiki

Thanks. We'll let you know when a new response is added.

If the NDF is a second file group then you will need to move the table into the PRIMARY file group.

This can be done in one of two ways.

1. Create a new table with the same schema, but a different name. Copy all the data from the old table to the new table. Delete the old table. Rename the new table to the original tables name. Create all the indexes that were on the old table.

2. Use the ALTER INDEX statement to change the filegroup of the clustered index on the table to the PRIMARY file group. The table will move along with the clustered index.

If they are part of the same filegroup use the DBCC SHRINKFILE statement with the EMPTYFILE switch to tell SQL Server to move all the data from the NDF to the MDF. Then you can drop the NDF.

Depending on how much data there is to move this could take hours.

or

It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.

DBCC SHRINKDATABASE (dbName)

Following is the script to shrink single file.

DBCC SHRINKFILE (logicalLogFileName)

To find logicalLogFileName following command has to be ran.

USE dbName
EXEC sp_helpfile

Let us understand this using database AdventureWorks.

/* Shrink Whole AdventureWorks Database */
DBCC SHRINKDATABASE (AdventureWorks)
GO
/* Get the Logical File Name */
USE AdventureWorks
EXEC sp_helpfile
GO
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
GO

Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.<img src=”http://www.pinaldave.com/bimg/shrinkdb.gif” alt=”Sql” />
<img src=”http://www.pinaldave.com/bimg/shrinkdb.gif” alt=”sql2″ />

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • A440guy
    How do I tell if the NDF file is in a second file group?
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following