October 25, 2011 at 7:24 am
I am working on a huge table partioned in 10(partitions).The data will be loaded in one partition each day.
Next day data in partion2 so on..until partition10.
on 11 day the partiton1 data should be switch to staging table truncate table and load the data in partition1..so on.
LIke FIFO(First in first out)
I have created the partitonscheme,partitionfunction,partition a table in 10.
How do i remove age data with scripts (table partitions) .
Ex:
Begin
If day 10 then
ALTER TABLE dbo.Partitaiontable SWITCH PARTITION 1 TO dbo.staging;
Truncate dbo.staging
Else if day 9 then
........
end
Let me know if i m not clear..
October 25, 2011 at 10:38 am
Let me see if I understand...
Day 11 you will switch out partition1 to staging table
truncate staging table
merge partition 1&2
add partition 11 (10)
Does this sound correct? Basically a sliding window on day?
Thanks,
Jared
Jared
CE - Microsoft
October 25, 2011 at 10:53 am
Here are the steps,
Day 11 switch out partition1 to staging table
truncate staging table
Load Day 11 data in Partition1 table.
Day 12 switch out partition2 to to staging table
truncate staging table
Load Day 12 data in Partition1 table.
It should be a continous process.
Please let me know if it is not still clear.
October 25, 2011 at 10:57 am
Can you paste the definition for your partition function and scheme please?
Thanks,
Jared
Jared
CE - Microsoft
October 25, 2011 at 11:48 am
Here is the sample.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 2, 3,4,5);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg);
October 25, 2011 at 11:52 am
Mvs2k11 (10/25/2011)
Here is the sample.CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 2, 3,4,5);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg);
Maybe a better question for me to ask is what you are partitioning on in the table? Column name and data type.
Jared
Jared
CE - Microsoft
October 25, 2011 at 12:20 pm
Column.
October 25, 2011 at 12:27 pm
How does it know what to partition on? A Date (i.e. month), a number that is inserted into the table (i.e. 1,2,3,4,5,6,7,8,9,10)? It looks like a number, but I don't understand how you partition on this in a 10 day cycle. Do you update all 9's to 10's after all of the 10's have been switched and truncated? This makes no sense to me the wayyou are explaining it. Please give the DDL and some saple data for your table.
Thanks,
Jared
Jared
CE - Microsoft
October 25, 2011 at 12:31 pm
I think I get it now. More of a round robin type thing. I just don't understand how the data is getting inserted. i.e. How do you prevent any data from getting entered on day 11 before you do the switch? Once you switch out the 1's and truncate how does the new data come in as 1's. What do you do with 2's the next day? I think you need a sliding window to make this work properly.
Jared
Jared
CE - Microsoft
October 25, 2011 at 12:31 pm
Mvs2k11 (10/25/2011)
Let me know if i m not clear..
You're not, and you're being very vague with your responses:
Column.
Also, you mention 10 partitions, and when asked for a sample of your scheme and function, we get this:
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 2, 3,4,5);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg);
Which is 5 partitions.
So, as reasonable vague an answer. No code to work with, no code to return with, unfortunately.
What you're doing is generally the right path.
Here's where all the switching necessities are (BOL):
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/e3318866-ff48-4603-a7af-046722a3d646.htm
Of particular note:
Nonpartitioned tables must have the same constraints as target partition. If you are adding a nonpartitioned table as a partition to an already existing partitioned table, there must be a constraint defined on the column of the source table that corresponds to the partition key of the target table. This makes sure that the range of values fits within the boundary values of the target partition.
What this means is your staging table is going to need to have its constraints modified each run.
How are you tracking the last successful partition switch? External table storing lastrun data, modification of a configuration table? You'll want to do this to make sure you don't accidentally run over things.
Your function is generic, from what it looks like. I assume you're just applying a '1' or a '5' to the data on inbound. If that's not the case you're probably looking to be modifying your partition function as well. That's a maintenance issue.
Partition Switching is not something you can just slap in place.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2011 at 12:44 pm
Evil Kraig F (10/25/2011)
Mvs2k11 (10/25/2011)
Let me know if i m not clear..You're not, and you're being very vague with your responses:
Column.
Also, you mention 10 partitions, and when asked for a sample of your scheme and function, we get this:
CREATE PARTITION FUNCTION myRangePF1 (int)AS RANGE LEFT FOR VALUES (1, 2, 3,4,5);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg);
Which is 5 partitions.
So, as reasonable vague an answer. No code to work with, no code to return with, unfortunately.
What you're doing is generally the right path.
Here's where all the switching necessities are (BOL):
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/e3318866-ff48-4603-a7af-046722a3d646.htm
Of particular note:
Nonpartitioned tables must have the same constraints as target partition. If you are adding a nonpartitioned table as a partition to an already existing partitioned table, there must be a constraint defined on the column of the source table that corresponds to the partition key of the target table. This makes sure that the range of values fits within the boundary values of the target partition.
What this means is your staging table is going to need to have its constraints modified each run.
How are you tracking the last successful partition switch? External table storing lastrun data, modification of a configuration table? You'll want to do this to make sure you don't accidentally run over things.
Your function is generic, from what it looks like. I assume you're just applying a '1' or a '5' to the data on inbound. If that's not the case you're probably looking to be modifying your partition function as well. That's a maintenance issue.
Partition Switching is not something you can just slap in place.
In my script I actually create the staging tables within the script on the partition schema. First I drop all non-clustered indexes, create staging tables on partition schema, switch partitions, bcp out data (not needed here), drop staging tables, and then recreate non-clustered indexes on tables. If you are not changing the partition function, this should work.
Thanks,
Jared
Jared
CE - Microsoft
October 25, 2011 at 1:01 pm
jared-709193 (10/25/2011)
In my script I actually create the staging tables within the script on the partition schema. First I drop all non-clustered indexes, create staging tables on partition schema, switch partitions, bcp out data (not needed here), drop staging tables, and then recreate non-clustered indexes on tables. If you are not changing the partition function, this should work.
Sounds like a plan. Sorry, my comments there were for the OP, I'm reasonably sure of your abilities. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2011 at 1:29 pm
Evil Kraig F (10/25/2011)
jared-709193 (10/25/2011)
In my script I actually create the staging tables within the script on the partition schema. First I drop all non-clustered indexes, create staging tables on partition schema, switch partitions, bcp out data (not needed here), drop staging tables, and then recreate non-clustered indexes on tables. If you are not changing the partition function, this should work.Sounds like a plan. Sorry, my comments there were for the OP, I'm reasonably sure of your abilities. 🙂
No worries, I knew that 🙂 My solution to having to change the partition function or create constraints on the staging table was to simply create that staging table on the fly on the partition schema. My script also removes replication first, then recreates all of the publications, articles, and subscriptions at the end. Whew! lol
Jared
Jared
CE - Microsoft
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply