Adding files to a filegroup

Question

Post reply

Adding files to a filegroup

S. Kusen

SSChampion

Points: 10936
More actions
October 6, 2008 at 6:59 pm

#123863

Hi all,
I have been having some performance issues with an existing data file that is approximately 90GB in size, not very large.
However, this DB gets pounded, and I was looking to get some suggestions on how to move data between data files.
Currently, I have 1 128GB LUN presented for this data file, and was looking to, at the current time, add a few data files to the primary file group to a set of 25GB LUN's that aren't being used currently to help alleviate some of the load.
However, the question is how would I be able to allow data to fill in those drives proportionally with existing data? Seems like only new data pages will get written to the new files, and I won't actually get any benefit from the existing load.
To my knowledge, DBCC SHRINKFILE(filename, EMPTYFILE) will try to empty everything from the existing file to the smaller 50GB total, to which 90 > 50, so no dice.
Would the best option be to have new LUN's presented (say three 50GB drives), add files there, and then empty and remove the existing file?
I'm also trying to limit downtime as well.
Please let me know if any other info that I could provide would be a benefit.
Thanks in advance for any help/suggestions.
Steve

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

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

Nikhil Shikarkhane Default port Points: 1498 More actions · Answer 1

I can think of only 2 options right now, both need downtime :-

1. Create new filegroups on the new 50GB Luns, drop and recreate clustered indexes on the new filegroups.

2. Partition the existing tables and include the new filegroups in your partition scheme.

or a slightly cheezy one

1. Create a database snapshot, point your application to the snapshot and let users read the data at least.

ps: they will not be able to read the data from tables being rebuilt.

S. Kusen SSChampion Points: 10936 More actions · Answer 2

Thanks for the reply. Your first option was the one that I figured was the likely choice, moving the tables to a new filegroup that was set up on the additional LUN's.

Steve

JeremyE SSCoach Points: 16199 More actions · Answer 3

SK (10/6/2008)
Would the best option be to have new LUN's presented (say three 50GB drives), add files there, and then empty and remove the existing file?

This is the option I would go with. SQL will fill the 3 50GB datafiles equally. This also avoids changing filegroups and does not require downtime.