How to remove the subscription from Publication in SQL Server Replication

 

1. On SQL Server 2000 connect to Enterprise Manager
On SQL Server 2005 / 2008 connect to SQL Server Management studio

2. Expand Replication à Publication, select the Publication from which the subscribers to be removed.
On SQL Server 2005 / 2008 from the Replication à Local Publication select the Publication

3. Right click on the Publication

4. Select the Subscription tab

5. Select the Subscriber and click Delete button, that’s it

6. If you hold the “Shift” key you can delete more than one subscriber. However you have to execute the sp_subscription_cleanup each subscribers manually.

7. Rather if you delete one subscriber at a time, it is simple and no need to execute the stored procedures at subscriber.

Can we have database on SIMPLE Recovery model for Transactional Replication?

Yes. We can have Publisher database on SIMPLE recovery model.

The reason is that;

• When executing, the Log Reader Agent first reads the publication transaction log and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data transactions that have been marked for replication. Next, the agent batch copies those transactions to the distribution database at the Distributor.
  
• The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.
  
• Only committed transactions are sent to the distribution database by LogReader agent.
  
• After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed.
  
• Finally, the agent marks the rows in the transaction log that are ready to be truncated. Rows still waiting to be replicated are not truncated
  
• The transaction log on the Publisher can be dumped without interfering with replication, because only transactions not marked for replication are purged.