Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Partition Splitting Problem SQL Server 2008 R2

Answered Partition Splitting Problem SQL Server 2008 R2

  • Wednesday, January 16, 2013 10:04 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I have monthly partition (Size 150 GB per month). I forgot to create partition for January-2013. So all the January Data went to the dummy partition I created for the last range. As microsoft recomends the last partition should be empty.

    I tried to create a partition for January and switch the data in the dummy partition to the newly created partition. But the script ran for more than a day without any data transfer.
    I am not sure how to create new partitions for the table now. The server is having 32 cores and 128GB memory. So I don't think transfering 50GB data (till Jan 12th) witll be a problem for it.

    Here is the script:

    ALTER DATABASE DBName ADD FILE
    (NAME =F_TBL_201301,FILENAME ="<location>\F_TBL_201301.ndf",
    SIZE = 50MB,    MAXSIZE = unlimited,    FILEGROWTH = 10%
    ) TO FILEGROUP FG_TBL

    ALTER PARTITION SCHEME PS_TBL NEXT USED [F_TBL_201301];

    ALTER PARTITION FUNCTION PF_MSCRaw() SPLIT RANGE (20130131)


    Masters.....CAN ANYBODY HELP???


    -Swaroop

     

All Replies

  • Friday, January 18, 2013 9:04 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Swaroopksahoo,

    After create a partition function and a partition scheme, you need create  a clustered index ON a partition scheme, details please see: http://sqlandme.com/2011/08/09/sql-server-how-to-partition-an-existing-table/ 

    Thanks,
    Eileen

     

    Eileen Zhao
    TechNet Community Support

     
  • Saturday, January 19, 2013 6:33 PM
     
     
    Sign In to Vote
    0

    The simplest way would be to

    1) create a compatible staging table on the same filegroup as the dummy partition.

    2) switch the dummy partition out to the staging table.

    3) Fix your partition scheme, which will be fast now that the dummy partition is empty.

    4) insert the data from the staging table back into the main table

    David

    David http://blogs.msdn.com/b/dbrowne/

     
  • Sunday, January 20, 2013 6:31 PM
     
     Answered
    Sign In to Vote
    0

    We are usually talking about partitioning of huge volume tables as it is a challenging task particularly more if they are critical mission tables where no enough room for a down time for these tables so you can re-partition any huge table even TB without a down time as follows:

    1.        Create another table Mytable_2 partitioned with the needed partitioning schema
    2.        Create a simple DTS job to import all data from the original table Mytable1 to Mytable2 which can takes long times for hours , days ..
    3.        Create a simple DWH script to warehouse all recent DML changes (Inserts/updates/deletes) happened on Mytable1 while DTS run and to do that you can read more at my article

    http://www.sqlserverpath.org/2012/02/07/data-warehousing-workshop-24/  

    Actually , the main key here is the step #3 which can achieve non-appreciable down time but you need to run it once a time at least directly prior to the  switch time between the 2 tables to mitigate the down time as much as you can to very few seconds

    Please let me know if any further help is needed

    Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

     
  • Monday, January 21, 2013 3:36 PM
     
     
    Sign In to Vote
    0

    Hi

    see the steps to do "

    http://www.aktechforum.blogspot.com/2012/04/sql-server-table-partition.html

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/