Post reply

Re-porption SQL Data files

  • September 28, 2016 at 12:29 pm

    #330595

    I have a single data file thats over 5tb in size and id like to split it up into multiple files across multiple drives. Whats the best way to go about doing this making sure that the single file is evenly distributed across the new files.

    I have spent some time googling it and i just cant seem to come up with the right keywords.

    Thanks

  • September 28, 2016 at 12:39 pm

    #1903754

    coreyjbaum (9/28/2016)

    I have a single data file thats over 5tb in size and id like to split it up into multiple files across multiple drives. Whats the best way to go about doing this making sure that the single file is evenly distributed across the new files.

    I have spent some time googling it and i just cant seem to come up with the right keywords.

    Thanks

    You can just google on: proportional fill

    That should give you the results you were looking for. You can refer to this article on SCC that give more info on how it works.

    http://www.sqlservercentral.com/blogs/mr-fox-sql/2016/02/24/sql-filegroupfile-proportional-fill-algorithm/

    Sue

  • October 7, 2016 at 5:46 am

    #1905352

    coreyjbaum (9/28/2016)

    I have a single data file thats over 5tb in size and id like to split it up into multiple files across multiple drives. Whats the best way to go about doing this making sure that the single file is evenly distributed across the new files.

    I have spent some time googling it and i just cant seem to come up with the right keywords.

    Thanks

    Ideally you'd need to create a new filegroup and create the new files in this group.

    Then you would move objects from the primary filegroup to the new filegroup. When y6ou've finished you'll need to reduce the size of the primary file in the primary filegroup

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 7, 2016 at 7:43 am

    #1905371

    coreyjbaum (9/28/2016)

    I have a single data file thats over 5tb in size and id like to split it up into multiple files across multiple drives. Whats the best way to go about doing this making sure that the single file is evenly distributed across the new files.

    I have spent some time googling it and i just cant seem to come up with the right keywords.

    Thanks

    The "Best" way is to understand what's in that data-file. For example, is a large amount of that data older data that will never again be modified as in an audit table or something like an invoice detail table (I refer to those as W.O.R.M) tables. Such tables are normally the largest tables in any database.

    Within those tables, is there any data that has truly expired as is no longer needed online? That would be the first thing to do is to save a final copy of that data in smaller, separate, throw away tables in a different database, back them up a final time, and then drop that temporary database. Don't bother deleting that data from the original tables yet, unless you REALLY need the space to do the rest of the work coming up.

    For the data in the WORM tables that you DO need to keep, partition it into more manageable pieces such as "by month". Again, this should be done in a separate database so that 1) you don't have to restore years of legacy data during a "get back in business" DR Restore and 2) none of the tables will come into play for index maintenance, etc, and 3) you're backups won't take so long because you won't have to backup years of data that will never change. Each month would be "packed" in a single file/filegroup, set to READ_ONLY, have two final backups to tape, and then never have to be backed up again except to occasionally refresh tapes.

    Once you have designed for and gotten a handle on all the WORM data and have removed it from your original database (file), then you can start optimizing what's left.

    Step 1, though, is understand what's in the data, Step 2 is make a plan, and Step 3 is follow that plan. If you need to make tweaks to the plan, make sure they're documented. When you're done, you should have full documentation as to where data is, how it's split, and how to maintain the automation that will continue the splits and moves of the new WORM data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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