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

Move physical database files?

We have SQL server setup on one of our machines that is partitioned out. We
have the OS on drive C: and drive E: setup to house data. The problem is we
forgot that SQL server by default houses the actual data files on the C
drive in the SQL server directory. Our databases are already setup. What is
the best way to move those physical data files OFF the C drive and onto the
E drive. I tried through Enterprise Manager, but wasn't quite sure how. I
also tried stopping the services and making a copy of the files onto the E
drive and then creating a new database pointing to those copied files, but
that didn't work either. What's the best way to go about this. Bottom line,
we don't want the data files on the same partition as the OS. THANKS.You can do it with the help of sp_detach_db and sp_attach_db.
first use the sp_detach_db command to detach the database.
Eg
sp_detach_db 'pubs'
copy data and tran. log files to the destination drive.
after copying the files, you can use the sp_attach_db stored procedure to reattach the files to SQL
Server with the new location
See more help on this in BOL.
--
- Vishal|||Hi
Best way to do it is right click on the database you wnt
to move and say "detach" (you won't be able to detach
system databases but you can probably just leave them on
your C drive) . Then move the data and log files to the
drive where you want them. Then right click on Databases
in Enterprise manager and go to "all tasks", "Attach
database". Browse for the *.mdf in yournew location..the
rest is self explanatory.
Enjoy
-Jono|||The detach/attach thing worked perfectly. THANKS.
"Jono" <false@.me.co.nz> wrote in message
news:26d7301c38ebd$69c8db40$a601280a@.phx.gbl...
> Hi
> Best way to do it is right click on the database you wnt
> to move and say "detach" (you won't be able to detach
> system databases but you can probably just leave them on
> your C drive) . Then move the data and log files to the
> drive where you want them. Then right click on Databases
> in Enterprise manager and go to "all tasks", "Attach
> database". Browse for the *.mdf in yournew location..the
> rest is self explanatory.
> Enjoy
> -Jono|||In addition to the other posts, if you need to move files for the system databases, read article
224071.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Erich" <erich93063@.hotmail.com> wrote in message news:uQeaJrrjDHA.392@.TK2MSFTNGP11.phx.gbl...
> We have SQL server setup on one of our machines that is partitioned out. We
> have the OS on drive C: and drive E: setup to house data. The problem is we
> forgot that SQL server by default houses the actual data files on the C
> drive in the SQL server directory. Our databases are already setup. What is
> the best way to move those physical data files OFF the C drive and onto the
> E drive. I tried through Enterprise Manager, but wasn't quite sure how. I
> also tried stopping the services and making a copy of the files onto the E
> drive and then creating a new database pointing to those copied files, but
> that didn't work either. What's the best way to go about this. Bottom line,
> we don't want the data files on the same partition as the OS. THANKS.
>