Problems with Switching partitions

Hi,

Im trying to switch a partition from partitioned table to a non partitioned table. I am getting the following error.

eCertified.dbo.Carrier_Trip_AvailabilitySwitch.PK_Carrier_Trip_AvailabilitySwitch_1' is in filegroup 'PRIMARY' and partition 24 of index 'eCertified.dbo.Carrier_Trip_Availability_Partition.PK_Carrier_Trip_Availability_Partition' is in filegroup 'SECONDARY24'. [SQLSTATE 42000] (Error 4939). The step failed.

The table one has 24 partitions and it has a partitioned clustered index. PK_Carrier_Trip_AvailabilitySwitch is the primary key in the non partitioned table and its is in the primary file group while the PK_Carrier_Trip_Availability_Partition is the primary key iin the partitioned table and its in the file group secondary 24.

Please let me know how to solve this issue.

When you switch a partition of a partitioned table to a non-partitioned table or a partition of another partitioned table, the source partition and the target table/partition must be in the same file group.

Problems with SQL 2000 with SP3

I have a Windows 2000 Server with SQL 2000 SP3 installed. It is partitioned
into to seperate logical drives. The first partition is the OS and the
second partition is SQL and its databases. Both partitions have seperate
RAID controllers and drives associated with it. I recently had problems my
RAID controller relating to the second partition. I was able to recover the
hardware failure and can still read the SQL partition and its data.
Unfortunately, I cannot get the SQL service to start anymore. I get the
following errors in event viewer:
Source: MSSQLSERVER
Category: (2)
Event ID: 17055
Description:
17052 :
Cannot recover the master database. Exiting.
Source: MSSQLSERVER
Category: (2)
Event ID: 17055
Description:
18052 :
Error: 9003, Severity: 20, State: 1.
Cannot anyone get me started on what I need to do? I have a backup of
everything but I didn't know if their was an easier way to get this working.
ThanksHave you considered uninstalling and reinstalling SQL Server completely?
Then it should just be a matter of restoring your user databases, which is
what you have backups for anyway...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Allison" <no@.email.com> wrote in message
news:uwW3RaJIEHA.3848@.tk2msftngp13.phx.gbl...
>I have a Windows 2000 Server with SQL 2000 SP3 installed. It is
>partitioned
> into to seperate logical drives. The first partition is the OS and the
> second partition is SQL and its databases. Both partitions have seperate
> RAID controllers and drives associated with it. I recently had problems
> my
> RAID controller relating to the second partition. I was able to recover
> the
> hardware failure and can still read the SQL partition and its data.
> Unfortunately, I cannot get the SQL service to start anymore. I get the
> following errors in event viewer:
> Source: MSSQLSERVER
> Category: (2)
> Event ID: 17055
> Description:
> 17052 :
> Cannot recover the master database. Exiting.
> Source: MSSQLSERVER
> Category: (2)
> Event ID: 17055
> Description:
> 18052 :
> Error: 9003, Severity: 20, State: 1.
> Cannot anyone get me started on what I need to do? I have a backup of
> everything but I didn't know if their was an easier way to get this
> working.
> Thanks
>|||From the error messages that I listed and the problem I have described, what
do you think happened? Was it the master database? Can't figure out why
the service will not start. Looks like all the data is there.
Thanks
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23qOL$oJIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> Have you considered uninstalling and reinstalling SQL Server completely?
> Then it should just be a matter of restoring your user databases, which is
> what you have backups for anyway...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
>
> "Allison" <no@.email.com> wrote in message
> news:uwW3RaJIEHA.3848@.tk2msftngp13.phx.gbl...
> >I have a Windows 2000 Server with SQL 2000 SP3 installed. It is
> >partitioned
> > into to seperate logical drives. The first partition is the OS and the
> > second partition is SQL and its databases. Both partitions have
seperate
> > RAID controllers and drives associated with it. I recently had problems
> > my
> > RAID controller relating to the second partition. I was able to recover
> > the
> > hardware failure and can still read the SQL partition and its data.
> > Unfortunately, I cannot get the SQL service to start anymore. I get the
> > following errors in event viewer:
> >
> > Source: MSSQLSERVER
> > Category: (2)
> > Event ID: 17055
> > Description:
> > 17052 :
> > Cannot recover the master database. Exiting.
> >
> > Source: MSSQLSERVER
> > Category: (2)
> > Event ID: 17055
> > Description:
> > 18052 :
> > Error: 9003, Severity: 20, State: 1.
> >
> > Cannot anyone get me started on what I need to do? I have a backup of
> > everything but I didn't know if their was an easier way to get this
> > working.
> >
> > Thanks
> >
> >
>|||Also, would there be a nice KB article describing SQL restore operations.
Thanks
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23qOL$oJIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> Have you considered uninstalling and reinstalling SQL Server completely?
> Then it should just be a matter of restoring your user databases, which is
> what you have backups for anyway...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
>
> "Allison" <no@.email.com> wrote in message
> news:uwW3RaJIEHA.3848@.tk2msftngp13.phx.gbl...
> >I have a Windows 2000 Server with SQL 2000 SP3 installed. It is
> >partitioned
> > into to seperate logical drives. The first partition is the OS and the
> > second partition is SQL and its databases. Both partitions have
seperate
> > RAID controllers and drives associated with it. I recently had problems
> > my
> > RAID controller relating to the second partition. I was able to recover
> > the
> > hardware failure and can still read the SQL partition and its data.
> > Unfortunately, I cannot get the SQL service to start anymore. I get the
> > following errors in event viewer:
> >
> > Source: MSSQLSERVER
> > Category: (2)
> > Event ID: 17055
> > Description:
> > 17052 :
> > Cannot recover the master database. Exiting.
> >
> > Source: MSSQLSERVER
> > Category: (2)
> > Event ID: 17055
> > Description:
> > 18052 :
> > Error: 9003, Severity: 20, State: 1.
> >
> > Cannot anyone get me started on what I need to do? I have a backup of
> > everything but I didn't know if their was an easier way to get this
> > working.
> >
> > Thanks
> >
> >
>|||Hi Allison,
Based on my experience, the error "Cannot recovery the master database"
always indicates the master database is corrupted. In this cause, it is
suspected the corruption of the master database is caused by the hardware
failure. Unfortunately since this is critical error on master, not much can
be done. Only possible thing we can do is creating similar master on some
empty device and load the old GOOD master backup and go from there.
1. Copy all the mdf and LDF to another location as a backup and verify that
a good backup exists.
2. Rebuild a new master database using Rebuild master utility. Note:
Rebuilding the master database removes all database objects and data. For
more information regarding Rebuild master utility, please refer to the
following articles.
How to rebuild the master database (Rebuild Master utility)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht
_install_8w8p.asp
Rebuild master Utility
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp
_rebuildm_6dbt.asp
3. To verify that the rebuild is successful, start SQL Server normally
4. Restore the master database from your old backup.
5. Reattach all the databases
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||> be done. Only possible thing we can do is creating similar master on some
> empty device and load the old GOOD master backup and go from there.
How about just uninstalling SQL Server and reinstalling, then restoring all
the *good* backups of the user databases? Surely we don't need that old
master database if we have acceptable backups of all the existing DBs? This
rebuild/re-attach process seems like far too much work, when a clean start
is probably the best thing to do anyway...
A|||Well, I think everything is working again. I uninstalled SQL 2000 and
reinstalled it with the same directory structure. At that same time, I kept
all 100 of my user databases in the same folder, therefore I didn't have to
restore over all of them. I then went into single-user mode and restore
only the master database. Once the master database restored, it
automatically attached to all 100 databases to SQL. I then exited
single-user mode, restarted the SQL service, and accessed my front-end
program. Everything looked good. I could access my databases and query
them. I'm still kind of suspicious because it wasn't as bad as I thought.
Hopefully I did it right.
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OLKcm$PIEHA.3248@.TK2MSFTNGP12.phx.gbl...
> > be done. Only possible thing we can do is creating similar master on
some
> > empty device and load the old GOOD master backup and go from there.
> How about just uninstalling SQL Server and reinstalling, then restoring
all
> the *good* backups of the user databases? Surely we don't need that old
> master database if we have acceptable backups of all the existing DBs?
This
> rebuild/re-attach process seems like far too much work, when a clean start
> is probably the best thing to do anyway...
> A
>

Problems with partitioned views and pruning

/*
problem: Trying to get partitioned views to "prune" unneeded
partitions from
select statements against the partitioned view. There are 5
partitioned
tables. Each with a check constraint based on a range of formula_id
column.

Test: Run this script to create the 5 partitioned tables and the
partitioned view. Then
run the explain plans on the select statements at the end of the
script and see that we
can only prune if we give a seemingly superfluous is not null
criteria in addition to
the formula_id.

Ideal: We want to only have to use the formula_id in the select
statement to prune.
*/

/*note: you may get errors on the drops first time run*/
drop table dbo.cs_working_e2
go
CREATE TABLE dbo.cs_working_e2 (
formula_id int NOT NULL
CONSTRAINT formula_id_e14
CHECK (formula_id between 1
and 1000),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3436
DEFAULT 1
CONSTRAINT Binary_flag_rule667
CHECK (authority_flag IN
(0, 1)),
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6926
DEFAULT 0
CONSTRAINT Binary_flag_rule668
CHECK (interpolated_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1807
DEFAULT getdate(),
CONSTRAINT XPKcs_working_e2
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go
CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_e2 ON dbo.cs_working_e2
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_e2 ON dbo.cs_working_e2
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go
drop table dbo.cs_working_indexes2
go
CREATE TABLE dbo.cs_working_indexes2 (
formula_id int NOT NULL
CONSTRAINT formula_id_indexes14
CHECK (formula_id between
7001 and 9000),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3437
DEFAULT 1
CONSTRAINT Binary_flag_rule669
CHECK (authority_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6927
DEFAULT 0
CONSTRAINT Binary_flag_rule670
CHECK (interpolated_flag IN
(0, 1)),
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1808
DEFAULT getdate(),
CONSTRAINT XPKcs_working_indexes2
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go

CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_indexes2 ON
dbo.cs_working_indexes2
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_indexes2 ON dbo.cs_working_indexes2
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go

drop table dbo.cs_working_other2
go
CREATE TABLE dbo.cs_working_other2 (
formula_id int NOT NULL
CONSTRAINT formula_id_other14
CHECK (formula_id >= 9001),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3438
DEFAULT 1
CONSTRAINT Binary_flag_rule671
CHECK (authority_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6928
DEFAULT 0
CONSTRAINT Binary_flag_rule672
CHECK (interpolated_flag IN
(0, 1)),
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1809
DEFAULT getdate(),
CONSTRAINT XPKcs_working_other2
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go

CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_other2 ON
dbo.cs_working_other2
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_other2 ON dbo.cs_working_other2
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go

drop table dbo.cs_working_p1q12
go
CREATE TABLE dbo.cs_working_p1q12 (
formula_id int NOT NULL
CONSTRAINT formula_id_p1q114
CHECK (formula_id between
3001 and 7000),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3439
DEFAULT 1
CONSTRAINT Binary_flag_rule673
CHECK (authority_flag IN
(0, 1)),
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6929
DEFAULT 0
CONSTRAINT Binary_flag_rule674
CHECK (interpolated_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1810
DEFAULT getdate(),
CONSTRAINT XPKcs_working_p1q12
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go

CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_p1q12 ON
dbo.cs_working_p1q12
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_p1q12 ON dbo.cs_working_p1q12
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go

drop table dbo.cs_working_pq2
go
CREATE TABLE dbo.cs_working_pq2 (
formula_id int NOT NULL
CONSTRAINT formula_id_pq14
CHECK (formula_id between
1001 and 3000),
submission_id int NOT NULL,
node_id int NOT NULL,
reference_year smallint NOT NULL,
observation_period datetime NOT NULL,
authority_flag tinyint NOT NULL
CONSTRAINT ONE_DEFAULT3440
DEFAULT 1
CONSTRAINT Binary_flag_rule675
CHECK (authority_flag IN
(0, 1)),
interpolated_flag tinyint NOT NULL
CONSTRAINT ZERO_DEFAULT6930
DEFAULT 0
CONSTRAINT Binary_flag_rule676
CHECK (interpolated_flag IN
(0, 1)),
observation_value decimal_datatype NOT NULL,
time_created smalldatetime NOT NULL
CONSTRAINT
CURRENT_DATE_DEFAULT1811
DEFAULT getdate(),
CONSTRAINT XPKcs_working_pq2
PRIMARY KEY NONCLUSTERED (formula_id, submission_id,
node_id, reference_year, observation_period)
--ON "INDEXES"
)
--ON "WORKING"
go

CREATE UNIQUE CLUSTERED INDEX XAK1cs_working_pq2 ON
dbo.cs_working_pq2
(
submission_id ASC,
formula_id ASC,
node_id ASC,
authority_flag ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
go

CREATE INDEX XIE1cs_working_pq2 ON dbo.cs_working_pq2
(
node_id ASC,
authority_flag ASC,
formula_id ASC,
observation_period ASC,
reference_year ASC,
observation_value ASC
)
--ON "INDEXES"
go

-- create view ---
drop view cs_working2
go
CREATE VIEW cs_working2 (submission_id, node_id, reference_year,
observation_period, formula_id, observation_value, interpolated_flag,
time_created, authority_flag) AS
SELECT we.submission_id, we.node_id, we.reference_year,
we.observation_period, we.formula_id, we.observation_value,
we.interpolated_flag, we.time_created, we.authority_flag
FROM cs_working_e2 we
union all
SELECT wo.submission_id, wo.node_id, wo.reference_year,
wo.observation_period, wo.formula_id, wo.observation_value,
wo.interpolated_flag, wo.time_created, wo.authority_flag
FROM cs_working_other2 wo
union all
SELECT wpq.submission_id, wpq.node_id, wpq.reference_year,
wpq.observation_period, wpq.formula_id, wpq.observation_value,
wpq.interpolated_flag, wpq.time_created, wpq.authority_flag
FROM cs_working_pq2 wpq
union all
SELECT wp1q1.submission_id, wp1q1.node_id, wp1q1.reference_year,
wp1q1.observation_period, wp1q1.formula_id, wp1q1.observation_value,
wp1q1.interpolated_flag, wp1q1.time_created, wp1q1.authority_flag
FROM cs_working_p1q12 wp1q1
union all
SELECT wi.submission_id, wi.node_id, wi.reference_year,
wi.observation_period, wi.formula_id, wi.observation_value,
wi.interpolated_flag, wi.time_created, wi.authority_flag
FROM cs_working_indexes2 wi
go

-- sample selects against partitioned view --
/*
--run explain plan here and see all 5 partitions being pulled
select * from cs_working

--run explain plan here and see just the 1 partition
select * from cs_working_e2

--run explain plan and see this is not pruning to the needed partition
select * from cs_working
where formula_id = 1

--run explain plan and see it is now pruning to the needed partition
select * from cs_working
where formula_id = 1
and submission_id is not null

--run explain plan and see it is now pruning to the needed partition,
too
select * from cs_working
where formula_id = 1
and observation_value is not null
*/[posted and mailed, please reply in news]

SM (murray_shane56@.hotmail.com) writes:
> problem: Trying to get partitioned views to "prune" unneeded partitions
> from select statements against the partitioned view. There are 5
> partitioned tables. Each with a check constraint based on a range of
> formula_id column.
> Test: Run this script to create the 5 partitioned tables and the
> partitioned view. Then run the explain plans on the select statements at
> the end of the script and see that we can only prune if we give a
> seemingly superfluous is not null criteria in addition to the
> formula_id.

I looked this, and indeed the behaviour is surprising. However, it is
benign. If you look closer at the plans, you see that there is a filter
which includes a STARTUP EXPR. What this is all about is that before
doing anyhing else, SQL Server filters on the partitoning column.

If you populate the table with some data, and run a query with
SET STATISTICS IO ON, you will something like this:

Table 'c'. Scan count 1, logical reads 2, physical reads 0, read-ahead
reads 0.
Table 'b'. Scan count 0, logical reads 0, physical reads 0, read-ahead
reads 0.
Table 'a'. Scan count 0, logical reads 0, physical reads 0, read-ahead
reads 0.

Thus, only one of the table is accessed.

So while it may not seem so at first glance, you do actually achieve
your goal of partitioning the column.

It seems that this happens because there more than column in the primary
key of the tables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp