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.