1

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

pshore73
  • 427
  • 3
  • 9

1 Answers1

3

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.

SqlWorldWide
  • 13,687
  • 3
  • 30
  • 54