Post reply

Reduce (.ndf) file size after import

  • November 15, 2019 at 8:18 pm

    #3698174

    When I create a data file (.ndf) and move the previous day's data from production server (SQL2014) to the archive server (SQL2017), the the file size increased to 4 times of the file size in production.

    I am using the below query to create the file group and the .ndf file.

    ALTER DATABASE DB_TRANS ADD FILEGROUP DB_TRANS_FG_20191114
    ALTER DATABASE DB_TRANS ADD FILE (NAME = DB_TRANS_FG_20191114_F00', FILENAME = '\\..\DATA\DB_TRANS_FG_20191112_F00.ndf', SIZE =2679, MAXSIZE = 50000, FILEGROWTH = 86) TO FILEGROUP DB_TRANS_FG_20191114

    Any help is highly appreciated.

  • November 15, 2019 at 8:32 pm

    #3698179

    file group definition does not matter for this.

     

    How you defined the tables on the destination server (e.g. heap/clustered,  and with/without compression) as well as how you copied the data is what will have most impact on the size.

     

  • November 15, 2019 at 10:16 pm

    #3698215

    Thanks for your response.

    I am creating the tables through Generate Scripts, and I've duplicate the non clustered index as it is in the production server.

    Regarding copying the data from Production, I am just doing simple insert as below

    INSERT INTO <Archive Table>
    Select * from <Production Table> WHERE date=<>

    • This reply was modified 5 years ago by  kpwaran.

Viewing 3 posts - 1 through 2 (of 2 total)

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