Recovery mode of Subscriber

Question

Recovery mode of Subscriber

vellenks

Right there with Babe

Points: 764
More actions
September 17, 2010 at 3:57 am

#228011

Hi,
I am having simple Transactional Replication running on a 2 TB database. I have both the Publisher and Subscriber set to Full recovery mode. I have transactional log backup and distribution back up running on both systems every hour. However, i noticed that the Log is not being truncated at the subscriber side only. Any idea why no truncation is happening please?
1. I have sync with backup enabled on Distribution.
2. The Log_reuse_wait at the subscriber is somehow set to Replication. Is that a normal or it should be at the publisher?
For now, i have set the Subscriber to Simple and running a full backup, hoping the log will be truncated after the backup.
Thanks,
Vellen

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's not normal, replication should only be a wait type at the publisher. Your solution won't fix anything, this has nothing to do with recovery models.

You need to do the following:

Create a transactional replication publication (on the subscriber)

Publish a single article (pick a small table)

Stop the log reader agent

Go to a query window and run the following:

exec sp_repldone

Delete the publication that you created.

After that the log reuse should not show replication and if you run DBCC OpenTran there should be no reference to distributed and non-distributed LSNs (which there will be now)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vellenks Right there with Babe Points: 764 More actions · Answer 2

Hi Gail,

Thanks for the quick response.

Just to confirm before i implement the new replication, I have run DBCC OpenTran at the subscriber and below is the result. Does this means there are lots of transactions waiting?

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (678229:118943:1)

Rgds,

Vellen

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It means there's a partially configured transactional replication publication at the subscriber, which from what you've said shouldn't be where.

Can't tell number of transactions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vellenks Right there with Babe Points: 764 More actions · Answer 4

Gail,

The steps you gave, worked wonderfully well. I did exactly what you said and Subscriber was not in Replication under Log_Reuse_Wait. I still perplexed how come this happened since the subscriber has never been published.

Anyway, the problem has been solved and thanks again.

Rgds,

Vellen

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

vellenks (9/17/2010)
I still perplexed how come this happened since the subscriber has never been published.

I'd love to know as well. Was it ever restored from a backup of a database that was published?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vellenks Right there with Babe Points: 764 More actions · Answer 6

yeah, good point; it might have come from the publisher.

Actually, we initialized the subscriber through datafile copy:

1. We configured the Distributor.

2. We configured the Publisher.

3. Set 'allow initialization through backup' on.

4. shut down Publisher and copy the file across.

5. Configure the Subscriber.

Probably the setting got copied from the publisher to the subscriber.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Very likely. Good to know.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

winash SSChampion Points: 11446 More actions · Answer 8

>>Set 'allow initialization through backup' on.

Did you turn this off on the publisher after the subscriber got synched with the publisher? It is not needed to be turned on once the subscriber is synched from the backup (unless you need it on).

From http://msdn.microsoft.com/en-us/library/ms152560.aspx:

Setting this option on the publication database ensures that transactions are not delivered to the distribution database until they are backed up at the publication database. The last publication database backup can then be restored at the Publisher without any chance of the distribution database having transactions that the restored publication database does not have.

Latency and throughput are affected because transactions cannot be delivered to the distribution database until they have been backed up at the Publisher. For example, if the transaction log is backed up every five minutes, there is an additional five minutes of latency between when a transaction is committed at the Publisher and when the transaction is delivered to the distribution database, and subsequently the Subscriber.