Primary Data Files

Question

Post reply

Primary Data Files

tmmutsetse

Hall of Fame

Points: 3669
More actions
July 9, 2020 at 2:31 pm

#3768701

Good day experts,
I added a second mdf file to my database but the second file is not writing any data.
Am i missing something? i am using standard 2014 edition and the settings are as below:

Thanks for your advise

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Will be down to the proportional fill algorithm thats used.

Adding a data file to an existing filegroup is generally not done due to this.

What would of been best is to add a new filegroup, add 2 files to that group then move the tables to that filegroup and then shrink down the first once its all migrated.

It's the same concept as for TempDB, files should be sized initially from the start, set to the same size from the start and grow the same size from the start.

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 2

Thanks Anthony for your help

If i get you well you are saying i must add a file group add 2 files which will be ndf not mdf and the push data from the primary to these 2 ndf files?

Thanks.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

tmmutsetse wrote:

Thanks Anthony for your help
If i get you well you are saying i must add a file group add 2 files which will be ndf not mdf and the push data from the primary to these 2 ndf files?
Thanks.

Not really - you can 'move' the data across the files by rebuilding the indexes. By using 2 new data files in a new file group - you still have to rebuild the indexes to move the data, but for that you then have to specify the new filegroup. This will probably need to be setup as a CREATE with DROP EXISTING instead of a simple rebuild.

Another - possibly better - option is to add 2 new files and then perform a DBCC SHRINKFILE(..., EMPTYFILE) on the original file. This tells SQL Server to empty the existing file and will proportionally fill the new files. Once the old file has been emptied you can then remove it.

One final note - if you have any tables setup as HEAPS they will need to be recreated on the new filegroup. That can be done either by creating a clustered index on the new filegroup or by creating a new table and moving the data.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

tmmutsetse wrote:

Good day experts,
I added a second mdf file to my database but the second file is not writing any data.
Am i missing something? i am using standard 2014 edition and the settings are as below:
Thanks for your advise

It is based the free space available in the staging file. I would suggest to disable the auto growth for staging file and staging_1 will grow further, I could see both are in same FG, it is ok to disable. I hope you goal is to stop grow of staging and point the future data to staging_1.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 5

Thanks for the advise ,the goal is for data to be written on both files.

This will reduce the growth rate of staging.

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 6

My DB is in Production ,1.4 TB in size .DBCC shrinkfile to another file group would be work but i wonder how long this will take for the 1.4 Tb