Post reply

Adding files to a filegroup

  • 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

  • October 8, 2008 at 7:02 pm

    #882656

    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.

  • October 8, 2008 at 9:50 pm

    #882687

    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

  • October 9, 2008 at 4:41 pm

    #883170

    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.

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

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