Post reply

delete data from partitions - faster approach

  • February 20, 2015 at 9:29 am

    #294795

    I am restoring a database with 10yrs worth of data which have monthly partitions but i would like to keep only 5yrs of data after the restore is done, what is the best/faster approach to delete the 5yrs data without deleting the partitions as that may cause the db in accessible.

  • February 20, 2015 at 10:29 am

    #1779007

    Tara-1044200 (2/20/2015)

    I am restoring a database with 10yrs worth of data which have monthly partitions but i would like to keep only 5yrs of data after the restore is done, what is the best/faster approach to delete the 5yrs data without deleting the partitions as that may cause the db in accessible.

    Partitioned table or partitioned view?

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

  • February 20, 2015 at 10:30 am

    #1779013

    table partitioned by month

  • February 20, 2015 at 11:54 am

    #1779034

    Tara-1044200 (2/20/2015)

    table partitioned by month

    Understood that from your original post. There are two types of partitioning in SQL Server. One is known as "partitioned views", which works on both Standard and Enterprise Editions, and "partitioned tables" which only works on the Enterprise Edition.

    There's quite a difference in how each is handled and to help you, we need more information, particularly as to which type of partitioning the database has.

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

  • February 20, 2015 at 12:14 pm

    #1779038

    partitioned tables using enterprise 2008.

  • February 25, 2015 at 4:24 pm

    #1780085

    Tara-1044200 (2/20/2015)

    partitioned tables using enterprise 2008.

    The proper way to do it would be to SWITCH out the month(s) of data that you want to delete, drop the switched out table(s), merge the months in the partition function and update the partition scheme so that the deleted month(s) are no longer a part of the scheme. It takes almost no time per month to do such a thing. None of that would that "may cause the db in accessible".

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

  • March 14, 2015 at 4:49 am

    #1784028

    Jeff, from what you are explaining it seems like there is a possibility with your approach. if could you please help with the sql for the steps you mentioned or refer me to any documents that would be great. Thanks

  • March 14, 2015 at 10:14 am

    #1784033

    Greg Robidoux has a short article on the subject. The keyword in SQL Server is "Switch". You might also want to look that word and the phrase "partitioned tables" up in "Books Online".

    http://www.mssqltips.com/sqlservertip/1406/switching-data-in-and-out-of-a-sql-server-2005-data-partition/

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

  • March 15, 2015 at 9:56 am

    #1784111

    I created copy of the table with same same structure and indexes and did

    ALTER TABLE [RevDB].[dbo].[BUDG] SWITCH PARTITION 11 TO [RevDB].[dbo].[budg_arc]

    the qry was successful but seems like it is copying data to table budg_arc behind the scene and can not access the table now. this partition has only 20k records and it is taking for ever, any adivse?

    Also i have tried this with single partition but i need to switch out data from 60 partitions and delete the switched out data, how do i do that? I mean how do i switch 60 partitions together.

  • March 15, 2015 at 11:29 am

    #1784116

    Switching that partition out like you did should be instantaneous because it only changes the meta data if you built the target table on the same file group (not even sure doing a SWITCH to a different file group is actually possible). Once the data has been "moved", you need to truncate the "new" table where you "moved" the data to and drop that table. That should be followed by a "MERGE" of the partitions in the partition function and the partition schema. That will require one set of such actions for each partition, which means that you'll probably need to write either a WHILE loop with some dynamic SQL or write some code to create and concatenate all the code for each partition at once using FOR XML PATH().

    For folks doing this the first time, I recommend using the loop.

    I don't have it with me at home but I have a "PartitionInfo" view that I wrote that can make all of this a bit easier. I'll see if I can dig it up tomorrow.

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

  • March 16, 2015 at 11:08 am

    #1784299

    Thanks Jeff, appreciate your response.

  • March 16, 2015 at 12:44 pm

    #1784321

    If i have to switch partitions for 60 times i understand i have to loop through for which i need a table that contentions partitions numbers and the file group so that which i know which partition is linked to which file group so that i can switch out the partitions for a particular year.

    Is there a system table that contains this info so i can loop through?

Viewing 12 posts - 1 through 11 (of 11 total)

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