1

I created 12 partitions from July 2017 through July 2018 and didn't leave an empty partition at the end. Hence, am having a hard time adding new partitions using the split function. I even tried switching the oldest partition to a staging table but "split range" is taking a very long time to execute. The goal of this project was to reuse the old partitions by archiving oldest partition to another table but I am not able to use the switch function as it's taking a very long time. Please advise so I can fix this issue. Here are the scripts:

CREATE PARTITION FUNCTION APITPartFunc AS RANGE LEFT
FOR
VALUES (
    '2018-01-31T00:00:00'
    ,N'2018-02-28T00:00:00'
    ,N'2018-03-31T00:00:00'
    ,N'2018-04-30T00:00:00'
    ,N'2018-05-31T00:00:00'
    ,N'2018-06-30T00:00:00'
    ,N'2018-07-31T00:00:00'
    ,N'2018-08-31T00:00:00'
    ,N'2018-09-30T00:00:00'
    ,N'2018-10-31T00:00:00'
    ,N'2018-11-30T00:00:00'
    ,N'2018-12-31T00:00:00'
    )

CREATE PARTITION SCHEME [APITScheme] AS PARTITION [APITPartFunc] TO (
    [ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ,[ApiTPartitions]
    ) 

   CREATE TABLE [dbo].[test] (
    [id] [bigint] IDENTITY(1, 1) NOT NULL
    ,[user_id] [uniqueidentifier] NOT NULL
    ,[client_id] [smallint] NOT NULL
    ,[api_tracking_type_id] [smallint] NOT NULL
    ,[dt] [datetime] NOT NULL
    ,[event_data] VARCHAR NULL
    ,[entity_id] VARCHAR NULL
    ) ON [APITPartScheme] (dt)
GO

ALTER TABLE [dbo].[test] ADD CONSTRAINT [PK_test_id_dt] PRIMARY KEY CLUSTERED (
    [id] ASC
    ,[dt]
    )
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,IGNORE_DUP_KEY = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ) ON APITPartScheme

--I was trying to add an empty partition by 
ALTER TABLE test switch PARTITION 13 TO teststaging PARTITION 13

ALTER PARTITION scheme apitpartscheme NEXT used apitpart13

ALTER PARTITION FUNCTION apitpartfunction () spit range ('2018-08-04')

ALTER TABLE teststaging switch PARTITION 13 TO test PARTITION 13
--this would be after split works but the above script took forever so I stopped it.
2
  • I guess you already have August data in your July partition. If so every row has to move physically to new partition which is expensive and slow. – SqlWorldWide Aug 3 '18 at 18:43
  • we took a downtime yesterday so no new records flow in active partition (july) and then I tried to switch partition 13 (july) to staging table so partition 13 was empty. I was hoping the split will be fast after switching data to staging but it wasn't. I am not sure why as the partition had 0 records when I switched it to the staging table – Neha Aug 3 '18 at 19:10