Moving a clustered index to a different partition scheme

> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy
> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=319924
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>

Moving a clustered index to a different partition scheme

Hello,
I am trying to use SSMS to move a clusted index to a new partitioned
scheme. I have done everything msdn said to do:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
When I change the partition scheme in the index properties (storage
tab) and hit ok or try to script it I get this error:
You cannot perform operation Remove on an object in state Existing.
(Microsoft.SqlServer.Smo)
I can not find this error anywhere.
We are trying to move the clustered index from a partitioned scheme on
a small sized drive to a new partitioned scheme on a larger drive.
Hoping this is will keep the partitions and move the data quickly, as
it a large table.
Any help would be greatly appreciated,
RandyTry just writing your own script. There are things that cannot be done
using SSMS. I don't know if this is one of them but writing your own script
is guaranteed to work (if you write it correctly using BOL as a guide).
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=319924
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
>> We are trying to move the clustered index from a partitioned scheme on
>> a small sized drive to a new partitioned scheme on a larger drive.
>> Hoping this is will keep the partitions and move the data quickly, as
>> it a large table.
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>> Hello,
>> I am trying to use SSMS to move a clusted index to a new partitioned
>> scheme. I have done everything msdn said to do:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
>> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
>> When I change the partition scheme in the index properties (storage
>> tab) and hit ok or try to script it I get this error:
>> You cannot perform operation Remove on an object in state Existing.
>> (Microsoft.SqlServer.Smo)
>> I can not find this error anywhere.
>> We are trying to move the clustered index from a partitioned scheme on
>> a small sized drive to a new partitioned scheme on a larger drive.
>> Hoping this is will keep the partitions and move the data quickly, as
>> it a large table.
>> Any help would be greatly appreciated,
>> Randy
>|||Hi,
Thank you for all the help, it is appreciated. We did write out the
script ourselves and learned more about it becasue of it.
This is the test script to setup the db, table, partitions and then
move the data:
use master
go
create database [test] on primary
( name = N'test', filename = N'C:\mssql\data\test.mdf' , size =26624kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = N'test_log', filename = N'C:\mssql\data\test_log.ldf' , size
= 24632kb , maxsize = 1048576kb , filegrowth = 10%)
go
use test
go
alter database test
add filegroup test_d
go
alter database test
add file
(
name = test_d1,
filename = 'C:\mssql\data\test_d1.ndf',
size = 512 KB,
maxsize = 5MB,
filegrowth = 5%
) to filegroup test_d
--
--alter database test
--modify filegroup test_d DEFAULT
create table dbo.tst(fld int, fld2 char(1), fld3 datetime)
insert dbo.tst values(3,'Y','20071031')
insert dbo.tst values(3,'Y','20071130')
insert dbo.tst values(3,'Y','20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3) with( drop_existing
= on)
on old_pscheme(fld3)
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction()
split range('20071231')
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction()
split range('20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
dbo.tst
create table dbo.tst_new(fld int, fld2 char(1), fld3 datetime) on
old_pscheme(fld3)
insert dbo.tst_new values(3,'Y','20071231')
insert dbo.tst_new values(3,'Y','20071031')
insert dbo.tst_new values(3,'Y','20071130')
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction() split range('20071130')
--drop index dbo_tst_cx on dbo.tst_new
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( drop_existing = on)
on old_pscheme(fld3)
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071130');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071231');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071031');
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
sp_help 'dbo.tst_new'
select object_name(object_id) o_id, * from sys.partitions where
object_name(object_id) = 'tst_new';sql

Moving a clustered index to a different partition scheme

Hello,
I am trying to use SSMS to move a clusted index to a new partitioned
scheme. I have done everything msdn said to do:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
When I change the partition scheme in the index properties (storage
tab) and hit ok or try to script it I get this error:
You cannot perform operation Remove on an object in state Existing.
(Microsoft.SqlServer.Smo)
I can not find this error anywhere.
We are trying to move the clustered index from a partitioned scheme on
a small sized drive to a new partitioned scheme on a larger drive.
Hoping this is will keep the partitions and move the data quickly, as
it a large table.
Any help would be greatly appreciated,
RandyTry just writing your own script. There are things that cannot be done
using SSMS. I don't know if this is one of them but writing your own script
is guaranteed to work (if you write it correctly using BOL as a guide).
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy|||> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLSe...=3199
24
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>|||Hi,
Thank you for all the help, it is appreciated. We did write out the
script ourselves and learned more about it becasue of it.
This is the test script to setup the db, table, partitions and then
move the data:
use master
go
create database [test] on primary
( name = N'test', filename = N'C:\mssql\data\test.mdf' , size =
26624kb , maxsize = unlimited, filegrowth = 1024kb )
log on
( name = N'test_log', filename = N'C:\mssql\data\test_log.ldf' , size
= 24632kb , maxsize = 1048576kb , filegrowth = 10%)
go
use test
go
alter database test
add filegroup test_d
go
alter database test
add file
(
name = test_d1,
filename = 'C:\mssql\data\test_d1.ndf',
size = 512 KB,
maxsize = 5MB,
filegrowth = 5%
) to filegroup test_d
--
--alter database test
--modify filegroup test_d DEFAULT
create table dbo.tst(fld int, fld2 char(1), fld3 datetime)
insert dbo.tst values(3,'Y','20071031')
insert dbo.tst values(3,'Y','20071130')
insert dbo.tst values(3,'Y','20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3) with( drop_existing
= on)
on old_pscheme(fld3)
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction()
split range('20071231')
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction()
split range('20071231')
create clustered index dbo_tst_cx on dbo.tst(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
dbo.tst
create table dbo.tst_new(fld int, fld2 char(1), fld3 datetime) on
old_pscheme(fld3)
insert dbo.tst_new values(3,'Y','20071231')
insert dbo.tst_new values(3,'Y','20071031')
insert dbo.tst_new values(3,'Y','20071130')
alter partition scheme old_pscheme next used [PRIMARY]
alter partition function old_pfunction() split range('20071130')
--drop index dbo_tst_cx on dbo.tst_new
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( drop_existing = on)
on old_pscheme(fld3)
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071130');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071231');
alter partition scheme asof_pscheme next used test_d
alter partition function asof_pfunction() split range('20071031');
create clustered index dbo_tst_cx on dbo.tst_new(fld3)
with( sort_in_tempdb = on, fillfactor = 100,drop_existing = on)
on asof_pscheme(fld3)
sp_help 'dbo.tst_new'
select object_name(object_id) o_id, * from sys.partitions where
object_name(object_id) = 'tst_new';

Move tempdb logfiles between partitions?

I am wondering if there's any issues associated with moving the tempdb
logfiles from one partition to another? If not, then what would you
recommend the best way to do this?http://support.microsoft.com/defaul...kb;en-us;224071
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<mikenospam72@.hotmail.com> wrote in message
news:1119212587.406860.87850@.f14g2000cwb.googlegroups.com...
>I am wondering if there's any issues associated with moving the tempdb
> logfiles from one partition to another? If not, then what would you
> recommend the best way to do this?
>

Move tempdb logfiles between partitions?

I am wondering if there's any issues associated with moving the tempdb
logfiles from one partition to another? If not, then what would you
recommend the best way to do this?
http://support.microsoft.com/default...b;en-us;224071
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<mikenospam72@.hotmail.com> wrote in message
news:1119212587.406860.87850@.f14g2000cwb.googlegro ups.com...
>I am wondering if there's any issues associated with moving the tempdb
> logfiles from one partition to another? If not, then what would you
> recommend the best way to do this?
>

Move tempdb logfiles between partitions?

I am wondering if there's any issues associated with moving the tempdb
logfiles from one partition to another? If not, then what would you
recommend the best way to do this?http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<mikenospam72@.hotmail.com> wrote in message
news:1119212587.406860.87850@.f14g2000cwb.googlegroups.com...
>I am wondering if there's any issues associated with moving the tempdb
> logfiles from one partition to another? If not, then what would you
> recommend the best way to do this?
>

Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:
> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do with
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
> >I have table with several million rows, which I would like to move to
> > partition schema.
> >
> > I've found this option is present in ALTER TABLE command. However, this
> > command
> >
> > alter table TaleName move to ps_Schema ( PartitionColumnName)
> >
> > resulted in error:
> >
> > Msg 102, Level 15, State 1, Line 1
> > Incorrect syntax near 'move'.
> >
> > I assume the syntax is wrong, but how to accomplish this task ? What is
> > the
> > correct syntax?
> >
> > Thank you very much.
> >
>
>

Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:

> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do wit
h
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>
>

Move database to another partition

I have recently installed SQL Server 2005 Standard Edition on a server with 2
partitions. I then installed Sharepoint which installed a database and put it
in the default location on the c: partition. I would like to move this to d:.
I have tried to do this by detaching and attaching, and also by backing and
restoring, but both the attach and the backup dialog boxes only allow me to
browse the c: drive. Is this a feature of Standard Edition? Do I need to use
Enterprise? Or is there a configuration setting I can change somewhere?
TIA25degc wrote:
> I have recently installed SQL Server 2005 Standard Edition on a server with 2
> partitions. I then installed Sharepoint which installed a database and put it
> in the default location on the c: partition. I would like to move this to d:.
> I have tried to do this by detaching and attaching, and also by backing and
> restoring, but both the attach and the backup dialog boxes only allow me to
> browse the c: drive. Is this a feature of Standard Edition? Do I need to use
> Enterprise? Or is there a configuration setting I can change somewhere?
> TIA
Not sure why you can't browse your other drive, but a workaround would
be to not use the GUI to do this. Instead use the SQL commands to
detach/reattach the database:
EXEC sp_detach_db DBname, 'true'
Copy the files from C: to D:
EXEC sp_attach_db @.dbname = 'DBName', @.filename1 = 'D:\MDFFile',
@.filename2 = 'D:\LDFFile'|||Thanks. This highlighted an issue with the d: drive. Although
readable/writable through Windows I decided to reformat, and now it is
working fine.
Thanks for your help.
"Tracy McKibben" wrote:
> 25degc wrote:
> > I have recently installed SQL Server 2005 Standard Edition on a server with 2
> > partitions. I then installed Sharepoint which installed a database and put it
> > in the default location on the c: partition. I would like to move this to d:.
> > I have tried to do this by detaching and attaching, and also by backing and
> > restoring, but both the attach and the backup dialog boxes only allow me to
> > browse the c: drive. Is this a feature of Standard Edition? Do I need to use
> > Enterprise? Or is there a configuration setting I can change somewhere?
> >
> > TIA
> Not sure why you can't browse your other drive, but a workaround would
> be to not use the GUI to do this. Instead use the SQL commands to
> detach/reattach the database:
> EXEC sp_detach_db DBname, 'true'
> Copy the files from C: to D:
> EXEC sp_attach_db @.dbname = 'DBName', @.filename1 = 'D:\MDFFile',
> @.filename2 = 'D:\LDFFile'
>

Move database to another partition

25degc wrote:
> I have recently installed SQL Server 2005 Standard Edition on a server wit
h 2
> partitions. I then installed Sharepoint which installed a database and put
it
> in the default location on the c: partition. I would like to move this to
d:.
> I have tried to do this by detaching and attaching, and also by backing an
d
> restoring, but both the attach and the backup dialog boxes only allow me t
o
> browse the c: drive. Is this a feature of Standard Edition? Do I need to u
se
> Enterprise? Or is there a configuration setting I can change somewhere?
> TIA
Not sure why you can't browse your other drive, but a workaround would
be to not use the GUI to do this. Instead use the SQL commands to
detach/reattach the database:
EXEC sp_detach_db DBname, 'true'
Copy the files from C: to D:
EXEC sp_attach_db @.dbname = 'DBName', @.filename1 = 'D:\MDFFile',
@.filename2 = 'D:\LDFFile'I have recently installed SQL Server 2005 Standard Edition on a server with
2
partitions. I then installed Sharepoint which installed a database and put i
t
in the default location on the c: partition. I would like to move this to d:
.
I have tried to do this by detaching and attaching, and also by backing and
restoring, but both the attach and the backup dialog boxes only allow me to
browse the c: drive. Is this a feature of Standard Edition? Do I need to use
Enterprise? Or is there a configuration setting I can change somewhere?
TIA|||25degc wrote:
> I have recently installed SQL Server 2005 Standard Edition on a server wit
h 2
> partitions. I then installed Sharepoint which installed a database and put
it
> in the default location on the c: partition. I would like to move this to
d:.
> I have tried to do this by detaching and attaching, and also by backing an
d
> restoring, but both the attach and the backup dialog boxes only allow me t
o
> browse the c: drive. Is this a feature of Standard Edition? Do I need to u
se
> Enterprise? Or is there a configuration setting I can change somewhere?
> TIA
Not sure why you can't browse your other drive, but a workaround would
be to not use the GUI to do this. Instead use the SQL commands to
detach/reattach the database:
EXEC sp_detach_db DBname, 'true'
Copy the files from C: to D:
EXEC sp_attach_db @.dbname = 'DBName', @.filename1 = 'D:\MDFFile',
@.filename2 = 'D:\LDFFile'|||Thanks. This highlighted an issue with the d: drive. Although
readable/writable through Windows I decided to reformat, and now it is
working fine.
Thanks for your help.
"Tracy McKibben" wrote:

> 25degc wrote:
> Not sure why you can't browse your other drive, but a workaround would
> be to not use the GUI to do this. Instead use the SQL commands to
> detach/reattach the database:
> EXEC sp_detach_db DBname, 'true'
> Copy the files from C: to D:
> EXEC sp_attach_db @.dbname = 'DBName', @.filename1 = 'D:\MDFFile',
> @.filename2 = 'D:\LDFFile'
>|||Thanks. This highlighted an issue with the d: drive. Although
readable/writable through Windows I decided to reformat, and now it is
working fine.
Thanks for your help.
"Tracy McKibben" wrote:

> 25degc wrote:
> Not sure why you can't browse your other drive, but a workaround would
> be to not use the GUI to do this. Instead use the SQL commands to
> detach/reattach the database:
> EXEC sp_detach_db DBname, 'true'
> Copy the files from C: to D:
> EXEC sp_attach_db @.dbname = 'DBName', @.filename1 = 'D:\MDFFile',
> @.filename2 = 'D:\LDFFile'
>

moving indexes onto another partition

Hi
From several weeks I suffer poor performance of my SQL Serv (as backend to
the web application).
I heard that sometimes splitting database to different hard disk could raise
performance. But in my case I have got only one HDD devided to 2 partition -
my question is: if it is good idea to migrate tables or indexes to anther
partition - does it make sens?
Or maybe thare is another solution to my problem - connected mostly with HDD
queue legth. So I could say that all the delay in answers are connected with
poor HDD performance.
Best Regards
Darek T.It does not help to move the indexes to the other partition: it is still the
same physical disk drive.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dariusz Tomon" wrote:

> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could rai
se
> performance. But in my case I have got only one HDD devided to 2 partition
-
> my question is: if it is good idea to migrate tables or indexes to anther
> partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with H
DD
> queue legth. So I could say that all the delay in answers are connected wi
th
> poor HDD performance.
>
> Best Regards
> Darek T.
>
>|||Use SQL profiler to run a trace and identify the query that is slow. The
vast majority of performance problems can be fixed by rewriting queries or
adding indexes.
Beyond that, you can only add physical drives to distribute the read/writes.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:O$Q3lgRdGHA.1656@.TK2MSFTNGP02.phx.gbl...
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could
> raise performance. But in my case I have got only one HDD devided to 2
> partition - my question is: if it is good idea to migrate tables or
> indexes to anther partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with
> HDD queue legth. So I could say that all the delay in answers are
> connected with poor HDD performance.
>
> Best Regards
> Darek T.
>

moving indexes onto another partition

Hi
From several weeks I suffer poor performance of my SQL Serv (as backend to
the web application).
I heard that sometimes splitting database to different hard disk could raise
performance. But in my case I have got only one HDD devided to 2 partition -
my question is: if it is good idea to migrate tables or indexes to anther
partition - does it make sens?
Or maybe thare is another solution to my problem - connected mostly with HDD
queue legth. So I could say that all the delay in answers are connected with
poor HDD performance.
Best Regards
Darek T.It does not help to move the indexes to the other partition: it is still the
same physical disk drive.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dariusz Tomon" wrote:
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could raise
> performance. But in my case I have got only one HDD devided to 2 partition -
> my question is: if it is good idea to migrate tables or indexes to anther
> partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with HDD
> queue legth. So I could say that all the delay in answers are connected with
> poor HDD performance.
>
> Best Regards
> Darek T.
>
>|||Use SQL profiler to run a trace and identify the query that is slow. The
vast majority of performance problems can be fixed by rewriting queries or
adding indexes.
Beyond that, you can only add physical drives to distribute the read/writes.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:O$Q3lgRdGHA.1656@.TK2MSFTNGP02.phx.gbl...
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could
> raise performance. But in my case I have got only one HDD devided to 2
> partition - my question is: if it is good idea to migrate tables or
> indexes to anther partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with
> HDD queue legth. So I could say that all the delay in answers are
> connected with poor HDD performance.
>
> Best Regards
> Darek T.
>

Moving Indexes a different partition

Hi,
Does SQL Server has it own Index file?
How do we move the index from one partition to another, say, C TO D.
Thank You
Indexes are stored in the data files. If you just want to move a particular
index from drive C to D, you need to make sure that it's the only thing in
the data file(s). You can control what is in which data file(s) by specifying
which filegroup an index should be created on. Note that a filegroup has one
or more data files, and a data file must be in one and only one filegroup.
So one way to move an index from C to D is create a new filegroup with its
files on D, and then drop the index and re-create it on the new filegroup.
Look up CREATE INDEX in the Books Online for its detailed syntax.
Linchi
"loufuki@.gmail.com" wrote:

> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
>
|||In addition to Linchi's answer , Indexes have pages not files. There are
data pages and index pages stored in data files
<loufuki@.gmail.com> wrote in message
news:b84a8468-2353-4e2e-9bb7-ac89db91c7b3@.q21g2000hsa.googlegroups.com...
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You

Moving Indexes a different partition

Hi,
Does SQL Server has it own Index file?
How do we move the index from one partition to another, say, C TO D.
Thank YouIndexes are stored in the data files. If you just want to move a particular
index from drive C to D, you need to make sure that it's the only thing in
the data file(s). You can control what is in which data file(s) by specifying
which filegroup an index should be created on. Note that a filegroup has one
or more data files, and a data file must be in one and only one filegroup.
So one way to move an index from C to D is create a new filegroup with its
files on D, and then drop the index and re-create it on the new filegroup.
Look up CREATE INDEX in the Books Online for its detailed syntax.
Linchi
"loufuki@.gmail.com" wrote:
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You
>|||In addition to Linchi's answer , Indexes have pages not files. There are
data pages and index pages stored in data files
<loufuki@.gmail.com> wrote in message
news:b84a8468-2353-4e2e-9bb7-ac89db91c7b3@.q21g2000hsa.googlegroups.com...
> Hi,
> Does SQL Server has it own Index file?
> How do we move the index from one partition to another, say, C TO D.
> Thank You