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.
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.
Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */
DBCC SHRINKDATABASE (AdventureWorks)
/* Get the Logical File Name */
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
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″ />