Post reply

Partition an existing SQL Server Table

  • June 23, 2015 at 5:13 pm

    #1807595

    What factors should be considered in the Strategy?

    He wants to put all but the last Year in a partition and then archive it.

    There is a disk limitation with the Transaction Log.

    What do you think?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 23, 2015 at 5:44 pm

    #1807596

    Jeff Moden (6/23/2015)

    Yep... Saw that. You'd be better off partitioning by year.

    He wants to partition 15 years into the First Partition and the current year in another partition

    Then archive all but the current year.

    Would that impact the size of the Transaction Log when archiving?

    Is this a good strategy?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 23, 2015 at 6:36 pm

    #1807598

    Welsh Corgi (6/23/2015)

    Jeff Moden (6/23/2015)

    Yep... Saw that. You'd be better off partitioning by year.

    He wants to partition 15 years into the First Partition and the current year in another partition

    Then archive all but the current year.

    Would that impact the size of the Transaction Log when archiving?

    Is this a good strategy?

    No. The current year only has a handful of rows. It would be much better to copy just the current year rows to a new table, archive the old table (final backup and drop), and then rename the new table to be the old table.

    Have you run sp_SpaceUsed on the table yet?

    --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)

  • June 23, 2015 at 6:56 pm

    #1807602

    Jeff Moden (6/23/2015)

    Welsh Corgi (6/23/2015)

    Jeff Moden (6/23/2015)

    Yep... Saw that. You'd be better off partitioning by year.

    He wants to partition 15 years into the First Partition and the current year in another partition

    Then archive all but the current year.

    Would that impact the size of the Transaction Log when archiving?

    Is this a good strategy?

    No. The current year only has a handful of rows. It would be much better to copy just the current year rows to a new table, archive the old table (final backup and drop), and then rename the new table to be the old table.

    Have you run sp_SpaceUsed on the table yet?

    There are 4 Tables that take up about half of a little 155 tiny GB Table.

    He is smart but he claims that the Transaction log will grow too large.

    I made sugesstions on how we could work around it but my recommendations were ignored.

    Thank you for your input.

    Thanks Jeff.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 23, 2015 at 7:23 pm

    #1807608

    Jeff Moden (6/23/2015)

    Welsh Corgi (6/23/2015)

    Jeff Moden (6/23/2015)

    Yep... Saw that. You'd be better off partitioning by year.

    He wants to partition 15 years into the First Partition and the current year in another partition

    Then archive all but the current year.

    Would that impact the size of the Transaction Log when archiving?

    Is this a good strategy?

    No. The current year only has a handful of rows. It would be much better to copy just the current year rows to a new table, archive the old table (final backup and drop), and then rename the new table to be the old table.

    Have you run sp_SpaceUsed on the table yet?

    I need to verify but 4 tables are taking up the majority of the space.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 23, 2015 at 7:29 pm

    #1807609

    How do I counter the Trans Logging issue?

    I know how to get around it.

    If he tries to delete record for 16 years then he is going to have a problem.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 24, 2015 at 7:41 am

    #1807740

    Again...

    1. With the understanding that the word "Archive" means "back it up and drop it from the database" to me, start off by taking a backup that you'll save to tape as an "archive".

    2. Copy the current very tiny current year to a new table. If you precreate the new table and then use WITH TABLOCK on the insert (whether you're using the FULL Recovery Model or not), then even that small amount of logging will be cut in half. And, no, you don't need Trace Flag 610 to do this.

    3. Copy the index and constraint definitions (if any) to a script and save the script.

    4. Drop the old table. Although not the correct term for it, this will appear to be "minimally logged" because only the page allocations are recorded. (TRUNCATE is actually "Fully Logged" but not the way people think and so a lot of people end up incorrectly saying that it's "Minimally Logged" but is even more pronounced than "minimal logging").

    5. Rename the new table to that of the old table.

    6. Apply the index and constraint script from step 3 to the new table.

    The overall effect will be a relatively tiny amount of logging compared to anything else you could do including partitioning and you will only have the current year left in the database,

    --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)

  • June 24, 2015 at 8:54 am

    #1807782

    Thanks Jeff!:-)

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 8 posts - 16 through 23 (of 23 total)

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