Sql-server – How to move data from Primary Data File to other data file without destroying replication

data-warehousereplicationsql serversql-server-2005

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 ?

Best Answer

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
Related Question