Adding a second data file

Question

Adding a second data file

sqldriver

SSChampion

Points: 10440
More actions
February 10, 2015 at 11:01 am

#294606

Hello,
I have a database that is approaching the 16TB data file limit, and currently at 11TB. During an upcoming maintenance window where it's getting moved to its own 20TB LUN, I plan on adding a second data file at the same time. No big deal. I get the proportional fill algorithm. IFI is turned up to 11. I'm scripting out incremental growths for the secondary data file, thinking about 512GB chunks.
So here's my WWYD? section:
1. New file group or same file group
2. One new file, or multiple
3. How would you size the new file(s)
4. Would you turn Autogrowth off for the mdf
5. I have a lot of LOB data. No question about it.
6. Index maintenance! Clustered rebuilds to a new FG are offline only. NCs are online. If I go with a new FG, should I rebuild my NCs to it explicitly or let the PFA work its magic?
7. Anything else you think would be smart
Thanks

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

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

sqldriver (2/10/2015)
No big deal. I get the proportional fill algorithm.

if one file is 11TB and you add a new file to the same filegroup. Proportional fill will just increase writes to the empty file.

When adding your file decide carefully whether you'll be creating a new filegroup or not. If creating a new filegroup what objects do you plan to move to that filegroup?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sqldriver SSChampion Points: 10440 More actions · Answer 2

Hi Perry,

Yeah, this is the first time I'm adding a file because I'm coming up against a limit. When I've done stuff like this before it's always been either to move archive data or to move specific items off to better/different storage. This isn't a case of either, so I'm kind of curious, if there was no prevailing plan for existing data, what would a thinking man do with this new file? :hehe:

Thanks