November 15, 2019 at 8:18 pm
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
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
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=<>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply