Post reply

Move data from one datafile to another

  • February 5, 2010 at 2:07 am

    #137123

    I have one huge db having size more than 200 GB in which we have 2 data files in primary filegroups. One is 104 GB nd other is 79 GB (newly created). Both files are on separate drives.Now what we have to do is that we have to empty this file nd move all data to that 79gb file nd then remove that 104gb file.How we can do this?

  • February 10, 2010 at 7:26 am

    #1116806

    No response yet:ermm:.Any clue might help.

    Vivek

  • February 10, 2010 at 7:41 am

    #1116823

    You can move data from one filegroup to another, but SQL manages where data goes if there are two files in the same filegroup. It uses a proportional fill algorithm.

    If you want to empty a file, you can use ShrinkFile with the EmptyFile option. Do note that you cannot do that to the first file of the primary filegroup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 10, 2010 at 8:14 am

    #1116856

    You can try following steps

    1)create a separate file group with diff name than primary

    2)create a table on this file group using "ON" clause of create table with diff table name

    3)move the data from source to destination

    4)once all the data is transferred to destination,drop the old table.

    note:Make sure you have that much space available on your Hard drive.

  • February 11, 2010 at 1:06 am

    #1117415

    You have not made it clear why you need to empty the second data file..or maybe I missed it. If you simply want to move the data off of the second drive you can just detach the DB, move the second file to the first drive, attach the database 'WITH move' or use the Attach Database GUI ..point it to the .mdf, it will complain about the missing data file but will let you 'find it'..and will complete the attach operation. From my viewpoint, 300GB in one datafile is a little over what I personally consider to be manageable from a 'what if I have to move it somewhere' standpoint. My experience is that you will see better multi-threading/parallelism/CPU usage with multiple data files... if you have multiple processors ...even if the data files are on the same disk. I wonder if you are expecting the performance hit you will probably take by directing all of your disk OPs to one disk/array instead of two disks/arrays.

    Of course, before doing any operation, you should protect your data...do a backup (with validation) to a drive that is not involved in these operations.

  • February 15, 2010 at 5:52 am

    #1118753

    As GilaMonster said to use emptyfile option, can it be done on the file which has some used extents.I am asking because I had performed DBCC FILESTATS and I saw there were some used extents in that file.Just wanted to confirm.

    Thanks

    Vivek

  • February 15, 2010 at 4:27 pm

    #1119001

    HI All,

    I have a similar requirement to complete.

    My DB has 6 data files of which 4 are on one drive and the remaining 2 are on a different drive(H).

    I want to remove one data file on the H: Drive

    Using DBCC SHRINKFILE EMPTYFILE Migrates all data from the specified file to other files in the same filegroup but,

    Can anyone suggest how to move date from one data file to another file (specifically to a particular destination file)

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

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