October 25, 2016 at 1:33 am
Hello,
we have table partitioned on DATE column, we create partition for Every Month, from Jan 2012 to Dec 2014 we have partitions created every month, but somehow for Jan 2015 partitions are got created for every day instead of month.
IT's almost 2 TB data size and now we want to resolve this issue. what are the ways we could resolve this issue as partitions are not getting created after 7 Jan 2015.
655362912014-06-01 00:00:00.000
655363012014-07-01 00:00:00.000
655363112014-08-01 00:00:00.000
655363212014-09-01 00:00:00.000
655363312014-10-01 00:00:00.000
655363412014-11-01 00:00:00.000
655363512014-12-01 00:00:00.000
655363612015-01-01 00:00:00.000
655363712015-01-02 00:00:00.000
655363812015-01-03 00:00:00.000
655363912015-01-04 00:00:00.000
655364012015-01-05 00:00:00.000
655364112015-01-06 00:00:00.000
655364212015-01-07 00:00:00.000
Regards,
Sachin
October 25, 2016 at 2:19 am
Can you show us the PARTION FUNCTION and the PARTION SCHEME (preferable by posting the DDL statements)? Without the definitions it is not possible for us to help you with your issue.
October 25, 2016 at 4:44 am
Hello,
Partition function and scheme as below
-- Step 1: Create Partition Functions and schema
CREATE PARTITION FUNCTION PF_MONTHLY (DATETIME)
AS RANGE LEFT FOR VALUES ('2014-01-01')
-- Step 2: Create partition scheme
print char(9)+'Create Partition Scheme'
CREATE PARTITION SCHEME PS_MONTHLY AS
PARTITION PF_MONTHLY ALL TO (DWH_PART)
Create Unique Clustered Index UQ_EVENT_LOG
On [dbuser].[EVENT_LOG](EVENT_TIME, ID)
On PS_MONTHLY(EVENT_TIME)
Alter Table [$(dbuser)].[event_log]
Add Constraint [PK_EVENT_LOG]
Primary Key NonClustered (ID)
On [DWH_PART]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply