SQL Server Corruption & Replication

Question

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

Answer 1

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.

• Add article to transactional publication without generating new snapshot
• http://ansqldba.blogspot.com/2012/02/adding-new-article-to-existing.html