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