6

Please help! I am having error switching out from the partition i set up. I have the scripts and error information below :

I created separate file groups for each range

--Create Partition Function

USE [ApplicationLogs]
GO
CREATE PARTITION FUNCTION [FN_SchedulerLog](datetime) AS RANGE LEFT FOR VALUES (N'2016-06-30T23:59:59.998', N'2016-07-31T23:59:59.998', N'2016-08-31T23:59:59.998', N'2016-09-30T23:59:59.998', N'2016-10-31T23:59:59.998', N'2016-11-30T23:59:59.998', N'2016-12-31T23:59:59.998')

--Create Parttion SCHEME
CREATE PARTITION SCHEME [sch_SchedulerLog] AS PARTITION [FN_SchedulerLog] TO ([FG_SchedulerLog_06_16], [FG_SchedulerLog_07_16], [FG_SchedulerLog_08_16], [FG_SchedulerLog_09_16], [FG_SchedulerLog_10_16], [FG_SchedulerLog_11_16], [FG_SchedulerLog_12_16], [PRIMARY])


DROP INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )


CREATE UNIQUE NONCLUSTERED INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


CREATE CLUSTERED INDEX [ClusteredIndex_on_sch_SchedulerLog_636102140668795637] ON [dbo].[SchedulerLog] 
(
    [Date]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [sch_SchedulerLog]([Date])


DROP INDEX [ClusteredIndex_on_sch_SchedulerLog_636102140668795637] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )



--- switching out data from any FGs to a switchout table for archiving purposes
--******************
--Create a switch out table: 
USE [ApplicationLogs]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[staging_SchedulerLog_20160923-120700](
    [ID] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HostName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HostIP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ModuleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Level] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Logger] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [FG_SchedulerLog_06_16]

USE [ApplicationLogs]
CREATE UNIQUE NONCLUSTERED INDEX [staging_SchedulerLog_20160923-120700_pkSchedulerLogId] ON [dbo].[staging_SchedulerLog_20160923-120700] 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FG_SchedulerLog_06_16]
USE [ApplicationLogs]
ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1] CHECK  ([Date]<=N'2016-06-30T23:59:59')
ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700] CHECK CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1]


---Switch out partition
ALTER TABLE [dbo].[SchedulerLog] SWITCH PARTITION 1 TO [dbo].[staging_SchedulerLog_20160923-120700];
GO

Error: ALTER TABLE SWITCH' statement failed. The table 'ApplicationLogs.dbo.SchedulerLog' is partitioned while index 'staging_SchedulerLog_20160923-120700_pkSchedulerLogId' is not partitioned.

  • 2
    Unless I'm missing something, the error message seems pretty clear. There's an index on your table that isn't aligned to the partitioning scheme. You won't be able to do any switching unless you drop that index. – Erik Darling Sep 24 '16 at 1:15
  • What @sp_BlitzErik said, the index staging_SchedulerLog_20160923-120700_pkSchedulerLogId isn't aligned and aligned + same indexes are required to switch the b-tree roots. – Sean Gallardy Sep 24 '16 at 1:16
  • Thank you so much. I was able to switch out after following the instructions. You guys are the best. – Djoe1 Sep 26 '16 at 18:27
4

Because switching partition change address of all pages to target table, it also moves indexes (created on the source table) to target table. If available index on the table is not partitioned index will not move to target.

When we SWITCH partition, make sure both (source and target) tables has same schema (structure), indexes (should be partitioned) and CHECK constraint.

According to the Error

Error: ALTER TABLE SWITCH' statement failed. The table 'ApplicationLogs.dbo.SchedulerLog' is partitioned while index 'staging_SchedulerLog_20160923-120700_pkSchedulerLogId' is not partitioned.

The index [pkSchedulerLogId] is not partitioned.

After you partition the index

DROP INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

CREATE UNIQUE NONCLUSTERED INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] 
(
    [ID] ASC
    ,[Date]
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [sch_SchedulerLog]([Date])

AND

either add check constraint to both tables

ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1] CHECK  ([Date]<=N'2016-06-30T23:59:59')
ALTER TABLE [dbo].[SchedulerLog]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_2] CHECK  ([Date]<=N'2016-06-30T23:59:59')

OR

remove constraint from staging table.

ALTER TABLE [staging_SchedulerLog_20160923-120700] DROP CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1];

Now you will be able to SWITCH partition

ALTER TABLE [dbo].[SchedulerLog] SWITCH PARTITION 1 TO [dbo].[staging_SchedulerLog_20160923-120700];

Thanks

  • Thank you so much. It worked perfectly after i partitioned the index and drop the constraint on my staging table. – Djoe1 Sep 26 '16 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.