Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have one Database which is replicated using transactional replication. Now, I want do spare some data of that database to another partition, because my server running out of disk space.

I was trying to move file to another filegroup trough SSMS using GUI tolls by clicking design at table and at properties tab changing "Regular Data Space Specification" to "secondary". When I try to save desing of table I get error Cannot drop the table 'dbo.MYTABLE' because it is being used for replication.

Here are a few questions:

  • Should I create data files at another partition in already existing filegroup "PRIMARY" or it is better to I open new filegroup and than in that new "secondary" filegroup to open new datafile?
  • Is there other way to I tell SQL to save data to another file besides dropping the table?
  • Is there way to I can temporarily exclude some tables from replication while I move them to another filegroup and then put them again in snapshot without having to reinitialize the subscriber ?
share|improve this question

1 Answer 1

up vote 5 down vote accepted

SSMS is creating a copy of the table, populating it, dropping the original. hence the error.

I'd create a new filegroup, rather then adding files to the primary filegroup because of how the files are used within a file group

You can move a table "in-situ" to a new filegroup by rebuilding the clustered index using CREATE INDEX .. DROP_EXISTING

CREATE NONCLUSTERED INDEX PK_Mytable
    ON dbo.Mytable(PKColumn)
    WITH (DROP_EXISTING = ON)
    ON NewFileGroup
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.