2

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 ?
7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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