Post reply

Splitting data files

  • May 26, 2011 at 5:30 am

    #145460

    I have a single data file of 350 GB in SQL Server 2000(Server A).

    We are migrating it to another server (Server B)with SQL Server 2008 there are drives less than 200 GB.

    Server A do not have any other drive and the space available on the drive is 60 GB.

    Disk space is available in another server to take normal backup.

    Please let me know how can I move the database from Server A to server B

  • May 26, 2011 at 5:44 am

    #1330202

    Hi,

    Although your file is 350GB...is the file completely full? If not and there is free space...you could shrink the file to a size that will allow to you restore on a smaller drive...that's only if you have enough free space to shrink the file to a smaller size.

    If you can't do the above, you would have to create additional smaller files on your existing enviroment and then move certain objects (like indexes) into these new files. This will allow then allow you to free up space on your mail file and you could then shrink it.

  • May 30, 2011 at 9:47 pm

    #1331771

    Thanks a lot for your answer.

    There is around 25 GB space available in the file.

    So as per my understanding I can create another datafile in the same drive and keep on moving some objects to the file.The file will continue to grow and free space from the main file.

    So this will be a recurrsive process of shrinking and moving the objects.

  • May 30, 2011 at 10:26 pm

    #1331775

    Additionally, I think your new file will have to be in a new file group so that you can specify the objects to move.

  • May 30, 2011 at 11:00 pm

    #1331785

    Create new FG and create 1 File in it and then move all non-clustered indexes in this FG then your master file and file group size will be reduce also.

    You can move easily Clustered indexes on another FG if column do not have any key , then this will also reduce the size of master file and file group

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • May 30, 2011 at 11:31 pm

    #1331788

    Thanks a lot for your help

Viewing 6 posts - 1 through 5 (of 5 total)

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