Post reply

Reorganize data

  • November 14, 2009 at 10:11 am

    #217624

    Hello all,

    just a quick question for the weekend.

    There´s a productive database, by now about 100GB.

    This DB will be moved from SQL 2005 to 2008.

    It depends on one filegroup with two data files, one called SYSTEM (which is the .mdf), another called USERDATA.

    I think this has to do with a migration from Orcale to SQL a long time ago (possibly in a galaxy far away... :hehe: )

    Problem is: SYSTEM is only 500MB, USERDATA 99GB.

    What I want to have is a database with two equal files on SQL 2008. On the new Server both files will be on separate LUNs.

    Also, I want to extend the data files to 200GB each because we´re expecting a higher data growth.

    So, what´s the best way to achieve that both data files are filled equally?

    I do not just want to expand SYSTEM and wait. I got already one suggestion by adding a new data file to the primary filegroup, expanding the the mdf and then shrink the USERDATA with emptyfile.

    But I think that´s not the smartest solution.

    Regards and nice weekend

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • November 16, 2009 at 6:27 am

    #1079625

    Hi Dirk

    Not knowing the details, I would really want to ask why you think that is what you want to do.

    Without you knowing it, you are probably in a fortunate position of having the primary database filegroup only containing system objects. This can give you a great bonus in terms of having filegroup/partial backups as a part of your strategy.

    If you really want 2 equal files, I would suggest you create a new file in the same group as userdata, and grow that to the same size. Leave them to both auto grow at a sensible rate - advise against % - and let it catch up. The disadvantage of this is that now older data will be on the old file, newer data will be on the new file, and once they equalise it will be interleaved between them.

    You wouldn't be doing yourself any favours by merging the data back into the system filegroup - even assuming you could.

    Best,

    Rich

  • November 16, 2009 at 6:57 am

    #1079644

    Hi Rich,

    thanks for the reply.

    The "problem" is that there is only the primary filegroup. With a small mdf and a big ndf.

    so, there is no separation between system tables and userdata.

    Both are merged into one filegroup

    Regards

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • November 16, 2009 at 9:33 am

    #1079757

    sweet 🙂

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

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