help with ALTER SWITCH statements on table PARTITIONS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

help with ALTER SWITCH statements on table PARTITIONS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-13 : 07:57:49

Jason writes "I am trying to create a running 12 month archive of error logs. I need to keep the previous months seperate from the log table for perfomance and other reasons. I am using SQL Server 2005.

I have 2 tables ErrorLogTest and ErrorLogTestArchive. I have created a partition on the first table. The partition function calls another function to return the 1st day of the current month (i need to partition the table so that partition 1 is last months data and partition 2 is this months). The ErrorLogTestArchive table has 13 partitions, basically 1 for each month of the year with 1 extra 1 off course.

I am trying to use an ALTER TABLE ... SWITCH statement (for speed reasaons, instead of insert and delete) to pull data from partition 1 on the ErrorLogTest table to partition X on the ErrorLogTestArchive table where X is last month as an int. e.g. when its Nov, i want to put Oct data on partition 10 of ErrorLogTestArchive (although at the moment, in the code below, im just putting it to 11).

For now i want to keep the partitions on the primary file group so lets not worry about that.

The problem is i can successfully take partition 1 from ErrorLogTest and SWITCH it to an empty ErrorLogTestArchive with no partitions but as soon as i try to SWITCH it to ErrorLogTestArchive when it does have partitions i get an error (see below). Heres the code it will help (note: the function dbo.fnc_GetMonthForPartitionSeparation just returns the 1st day of the month at 00:00:00.000 for the current month) :

create partition function parfnc_ErrorLogTestPartition ( datetime )
as range left
for values ( dbo.fnc_GetMonthForPartitionSeparation( getdate() ) )

create partition scheme parsch_ErrorLogPartitionScheme
as partition parfnc_ErrorLogTestPartition
to ([primary], [primary])

create table [dbo].[ErrorLogTest]
(
[ErrorId] [bigint] identity (1, 1) not null ,
[ProcName] [varchar] (100) null ,
[Statement] [varchar] (500) null ,
[ErrorMsg] [varchar] (100) null ,
[CreateDate] [datetime] not null,
constraint [PK_ErrorLogTest_ErrorID] primary key clustered
(
[ErrorId],
[CreateDate]
)with (PAD_INDEX = off, IGNORE_DUP_KEY = off) on parsch_ErrorLogPartitionScheme( [CreateDate] )
) on parsch_ErrorLogPartitionScheme( [CreateDate] )

create partition function parfnc_ErrorLogTestArchivePartition ( datetime )
as range right
for values (
'1 Jan 2006',
'1 Feb 2006',
'1 Mar 2006',
'1 Apr 2006',
'1 May 2006',
'1 Jun 2006',
'1 Jul 2006',
'1 Aug 2006',
'1 Sep 2006',
'1 Oct 2006',
'1 Nov 2006',
'1 Dec 2006'
)

create partition scheme parsch_ErrorLogArchivePartitionScheme
as partition parfnc_ErrorLogTestArchivePartition
all to ([primary])

create table [dbo].[ErrorLogTestArchive]
(
[ErrorId] [bigint] identity (1, 1) not null ,
[ProcName] [varchar] (100) null ,
[Statement] [varchar] (500) null ,
[ErrorMsg] [varchar] (100) null ,
[CreateDate] [datetime] not null,
constraint [PK_ErrorLogTestArchive_ErrorID] primary key clustered
(
[ErrorId],
[CreateDate]
)with (PAD_INDEX = off, IGNORE_DUP_KEY = off) on parsch_ErrorLogArchivePartitionScheme( [CreateDate] )
) on parsch_ErrorLogArchivePartitionScheme( [CreateDate] )

I populate the ErrorLogTest table with errors for Oct and Nov, then try to push the Oct data in partition 1 (which it is i checked) to partition 11

alter table PPMS.dbo.ErrorLogTest switch partition 1 to dbo.ErrorLogTestArchive partition 11

I actually get this error:

ALTER TABLE SWITCH statement failed. Range defined by partition 1 in table 'PPMS.dbo.ErrorLogTest' is not a subset of range defined by partition 11 in table 'PPMS.db

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources