September 6, 2016 at 10:47 am
I have a large database that is growing about 33GB a week it is currently at 1.7TB. As you may have guessed I am running out of space and I want to added a ndf file on a new drive but I have a few questions about doing this.
First is the new data automatically written to the new ndf file or how does it determine where to write the data.
Does it matter that one particular table is really the only table being written to?
Finally I am hoping I can just add a new ndf without any impact to the database as this is in production. Backup will be take prior as always 🙂
A little unrelated but when I remove an unused table to free up space there is no way to reclaim this space back without shrinking the db? Or does the space just not show as free to the OS but is free to the DB?
September 6, 2016 at 11:18 am
matthewful3 (9/6/2016)
First is the new data automatically written to the new ndf file or how does it determine where to write the data.Does it matter that one particular table is really the only table being written to?
Finally I am hoping I can just add a new ndf without any impact to the database as this is in production. Backup will be take prior as always 🙂
A little unrelated but when I remove an unused table to free up space there is no way to reclaim this space back without shrinking the db? Or does the space just not show as free to the OS but is free to the DB?
Hi Matthew, and welcome.
matthewful3 (9/6/2016)
First is the new data automatically written to the new ndf file or how does it determine where to write the data.
Yes,the new file. If you add the new file as part of the same filegroup as the existing file (should be the default), SQL uses a proportional fill algorithm to determine where to write the data. So your new file being empty will receive almost all of the new data being written.
Does it matter that one particular table is really the only table being written to?
No. All based on filegroups as stated above.
Finally I am hoping I can just add a new ndf without any impact to the database as this is in production. Backup will be take prior as always 🙂
Test if you can. SQL will need to allocate the space for the new file, but the metadata operation of adding the file should be pretty instant.
Should be fine, as long as you have instant file initialisation granted to the SQL service account.
A little unrelated but when I remove an unused table to free up space there is no way to reclaim this space back without shrinking the db? Or does the space just not show as free to the OS but is free to the DB?
Yes, after the delete the freed space will be available to the database but not the OS.
You can shrink the DB but be aware it can lead to high index fragmentation, and if you're continuing to write to the DB then you'll just be reusing the freed space anyway, so why not leave it allocated to SQL and save it having to grow the file again?
Cheers
September 6, 2016 at 12:24 pm
Thanks for answering my questions Gazareth!
September 6, 2016 at 12:51 pm
No problem, and good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply