I have a partitioned table that is published for transactional replication. The SWITCH operations on this table appears to replicated to the subscriber even though the options to allow_partition_switch = 1 and replicate_partition_switch = 0 on the publication.
When I try to execute the following sample script on the publisher ...
ALTER TABLE [PartitionedTable] SWITCH PARTITION 21 TO [PartitionedTable_SWAP] PARTITION 21
The following error is returned, and a stack dump is generated.
A user request from the session with SPID 53 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
Dropping the publication allows the SWITCH PARTITION statement to execute as expected. However, why is transaction replication clearly either trying to replicate the SWITCH operation, or at the very least, is dependent upon the SWITCH operation occurrence on the publisher? Is this a SQL Server bug? (Maybe fixed in a later SP or CU release??)
Another question is if I were to apply a filter on the article, would this resolve the issue?
Sean Fitzgerald
SQL Server 2008 Service Pack 2 was released in August, so it might be worth a try if you have the luxury of time to try to resolve it yourself. Alternatively, can you replicate in a fresh build?
SQL Server 2008 Service Pack 2: http://www.microsoft.com/download/en/details.aspx?id=12548