How to delete secondary data file by moving it's data to Primary data file

  • Hi,

    I have a database with one mdf file and one ldf file. Now I have created a secondary data file .ndf on separate drive and it's been in use for last 1 week

    In case, for whatever reasons, if we do not want the secondary data file .ndf and decided to remove it, then how to move the data from secondary data file to Primary data file and then delete the secondary data file?

    Is that possible?

    Please advice me

  • Hi gmamata,

    Check this.

    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 AdventureWorks2008R2;


    -- Create a data file and assume it contains data.

    ALTER DATABASE AdventureWorks2008R2

    ADD FILE (

    NAME = Test1data,

    FILENAME = 'C:\t1data.ndf',

    SIZE = 5MB



    -- Empty the data file.



    -- Remove the data file from the database.

    ALTER DATABASE AdventureWorks2008R2

    REMOVE FILE Test1data;


    Thank You,

    Best Regards,


  • Sqlbuddy,

    The Procedure discussed here is emptying the file but where the data is going to?

    I need to move the data from Secondary data file to Primary data file..and then delete the secondary file?


  • You can only move data by moving the clustered index (Rebuilding it) on the other filegroup. There is an "ON" clause for specifying the location.

    If you have other objects, like stored procs, in the new filegroup, you have to drop and recreate them.

  • This is Share point content database and it MUST have only one file group (multiple file groups are NOT supported for Share point content database)

    In this case, how can I move the data from secondary data file to primary data file in the same file group and then delete the secondary data file?


  • Hi gmamata7,

    The data will be moved from the secondary file to the primary file withing the same filegroup. Once this is done you can remove the secondary file using the ALTER DATABASE... command as mention in the example.

    Thank You,

    Best Regards,


  • sorry for adding my reply to this old thread.

    am using SQL Server 2019 and created a secondary file to fix the disk full error. Unfortunately, the secondary file is created in the PRIMARY filegroup. Now I want to move the secondary file content to the primary file (.MDF) and delete the secondary file. Please let me know, how to do it.

    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

  • Hope i can use this... but still hesitant to use it in production...

    USE [dbname]




    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply