October 17, 2011 at 10:03 pm
Hello,
The other day, I had to move the log file to another drive on the server. I ran sp_removedbreplication so I could detach the db, then attach with the new file locations.
Now I am trying to move some clustered indexes and getting an error. Here is the sql and the error:
ALTER TABLE Carts
DROP CONSTRAINT [PK_RowID_Carts] WITH (MOVE TO SECONDARY)
GO
ALTER TABLE Carts
ADD CONSTRAINT [PK_RowID_Carts] PRIMARY KEY CLUSTERED
( id ASC
)WITH (IGNORE_DUP_KEY = OFF) ON SECONDARY
GO
Msg 4929, Level 16, State 1, Line 2
Cannot alter the table 'Carts' because it is being published for replication.
Please let me know what steps to take to remove this publication. When I expand Replication > Local Publications, there is no instance. So I guess there is some table that still has all the articles listed to prevent most DDL changes.... if the publication still existed, I could alter it to "Replicate Schema Changes".
Thanks in advance,
-Colin
October 18, 2011 at 2:44 pm
The only way I have found to do this is to setup replication again with the same name, and go in and uncheck the article of the table that i want to move. no other sp_ for replication fixed this issue.
October 27, 2011 at 7:46 am
Using this option is better than detaching the database.
ALTER DATABASE <DBName> MODIFY FILE (NAME = '<logicalFile>', FILENAME = '<physicalname>')
With this we could avoid using sp_replicationdboption to remove replication.
M&M
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply