SQL Server – Handling Corruption and Replication Issues

corruptionreplicationsql server

I have a situation where storage was ripped out from under SQL Server. After running BBCC CheckDB I have received an "Extent (1:269886) in database ID 34 is allocated by more than one allocation object" message.

The research I have done basically tells me recover from backup. However, this table is a subscriber in transactional replication. Replication is set up such that I have added individual articles in the past in order to initialize in stages. Am I correct that I can do the following:

  1. Remove the article from replication
  2. Add the article back
  3. Let the agent job take a new snapshot
  4. Let that snapshot apply

Do I need to truncate or drop the damaged table first?

SQL Server 2008R2 on Windows Server 2008

Best Answer

Your steps are correct and you do not need to truncate or drop as you will be generating new snapshot for that one article.

Few things to check:

  • When you drop article, also drop subscription.
  • Make sure immediate_sync property of your publication is set to false so you do not end up with a full snapshot (of all articles).

These two threads has details about where things might go wrong.

Related Question