November 9, 2010 at 8:59 am
Hello,
In our company we have the following scenario
table MRegistration (which stands for Machine Registration) we track the values of sensors(registers) on various machines in the factories.
The table looks as followed:
MachineId (PK, int) not null
MachineRegisterId (PK,int)not null
UtcTime (PK,datetime)not null
Value (float)null
We got an ssis task that transfers all the data from the warehouse database to this table, this way we got 1 table that's easier to read and make reports on.
Now we like to partition this table and use an automated sliding window.
We want to partition per month.
For this I created the following filegroups and their corresponding files: JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
I also created a table called MRegPartioned which I partitioned on the filegroups and filled with the data from Mregistration.
I followed various steps and examples on the internet to create a sliding window and in the end I get stuck.
alter table machine.MRegistrationPartioned switch partition 2 to machine.staging_MRegistrationPartioned_20101109
Gives off the error Msg 4939, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. index 'TR_AIRCONNECT_MachineRegistration.machine.staging_MRegistrationPartioned_20101109.staging_MRegistrationPartioned_20101109_PK_MRegistrationPartioned' is in filegroup 'PRIMARY' and partition 2 of index 'TR_AIRCONNECT_MachineRegistration.machine.MRegistrationPartioned.PK_MRegistrationPartioned' is in filegroup 'MAR'.
This is not my only problem tough, on a Test database I preceded on a slightly different pad putting all my partitions on Primary and I get the following error
Msg 7733, Level 16, State 4, Line 1
'ALTER TABLE SWITCH' statement failed. The table 'John2.dbo.FactInternetSales' is partitioned while index 'PK_FactInternetSales' is not partitioned.
I also get error that either the indexes don't match.
In the end I only managed to switch out data between 2 tables that have no indexes, primary keys or constraints on them.
So if anyone can tell me what I'm doing wrong or has a good tutorial to do this please post.
November 9, 2010 at 9:48 am
Partitioning is sensitive to the indexes including columns that have been implicitely included in the index.
Create some small test tables to try it on.
This includes a lot of switching - for 2005 but shuold be the similar.
http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/
Cursors never.
DTS - only when needed and never to control.
November 10, 2010 at 3:45 am
Ok I now understand a few things I wasn't getting
For instance I was getting errors on cl_ix, and I hadn't the fogiest idea why.
So now that I know why, I can try and do a sliding window on our scenario.
Well at least test out and try.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply