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


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.

  • just a quote,in sql server 2016,you can drop article with out reinitializing replication – TheGameiswar Jun 13 '17 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.