Post reply

How to split a datafile

  • July 25, 2007 at 7:34 am

    #63082

    Hi, I've a question about to "How to split a datafile".

    For example:

    I've a "Mydb" with one 150GB Datafile, now I want to split this file into 4 files of 50GB.

    It's possible?

    Which is the best practice?

    Thanks a lot.

  • July 25, 2007 at 7:59 am

    #722290

    You could do this by first creating 4 additional files.

    Then run dbcc shrinkfile (file_id, emptyfile) on the original file, which will migrate the data in the original file and spread it across the 4 new files.  Then you can delete the original file, leaving with the 4 new files.

    That's one possible way of doing it.

    Karl
    source control for SQL Server

  • July 25, 2007 at 8:20 am

    #722305

    Create 4 new files move the data from the tables to the new files with a backup name and drop the tables and other objects in the database then create the objects again and bring the data back. Possibly not the best idea but i can't foresee anything more than this.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 26, 2007 at 3:47 am

    #722507

    Thanks, your solution works fine for me.

    I must to rebuild the clustered indexes?

  • July 26, 2007 at 3:59 am

    #722509

    I can't say for sure whether you'd need to rebuild your clustered indexes but it's always worthwhile running a dbcc showcontig regularly and rebuilding your indexes if they need to be.

    Karl
    source control for SQL Server

  • July 27, 2007 at 4:32 am

    #722811

    Thanks !!!

    Bye

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

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