SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction log in Subscriber database for Transactional Replication


Transaction log in Subscriber database for Transactional Replication

Author
Message
Willem Tilstra-440027
Willem Tilstra-440027
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 681
I have created replication between two SQL Server 2008R2 databases and it has been functioning well since the initial setup 3 months ago. The publisher database is the back-end for an OLTP application with 50 users who are working constantly, making changes and add new information. The Subscriber database is used for reporting.

Today I noticed that the Subscriber database was set to Full Recovery Mode with no transaction log backups. I panicked for a minute and took a quick look at the Transaction Log drive on the Subscriber server - it has 50% free space, 24 GB. I was amazed that I had been avoiding what normally would cause a drive-full issue.

My question is this. Since replication is constantly making changes to the Subscriber database (updating various tables as they change in the Publisher database), why is the Subscriber log file not filling up and giving me grief since I don't run backups on the Subscriber database - and haven't done for the entire three months this has been running? Gail Shaw mentioned in another forum question while I was researching this, that 'Replication does not in any way use the Subscriber's log file'. I'm not sure that I understand the seeming contradiction been the updating going on in the Subscriber database, Full Recovery mode, no Transaction Log backups taking place and the Transaction Log not filling up.

Can anyone enlighten me and/or point me to articles that might help me understand this more thoroughly??

Thank you.

Willem
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228445 Visits: 46342
Willem Tilstra-440027 (3/4/2013)
Gail Shaw mentioned in another forum question while I was researching this, that 'Replication does not in any way use the Subscriber's log file'.


Whoever said that should be taken out and shot. Oh, wait... :-D

The replication agents don't in any way read from or directly write to the subscriber's log, the subscriber's log (unless it's republishing) is just a normal transaction log used for normal database logging

I'm not sure that I understand the seeming contradiction been the updating going on in the Subscriber database, Full Recovery mode, no Transaction Log backups taking place and the Transaction Log not filling up.


Have you ever taken a full backup of the subscriber? If not, then the DB is in pseudo-simple recovery and the log is automatically being reused. It'll stay that way until someone takes a full backup.
Have you got any process (manual or automatic) switching the DB to simple recovery and back to full? If so, the DB is in pseudo-simple recovery until a full backup is taken.

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


Willem Tilstra-440027
Willem Tilstra-440027
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 681
Gail, thanks for the reply. I had forgotten about the pseudo-simple mode and I'll bet that is what is going on - I can't think of any other way that this transaction log isn't blowing up. I will set that Subscriber database back to Simple Recovery Mode so I can start breathing again.

To summarize then - the transaction log on the Subscriber database does get written to and does grow when there are inserts to that database because of activity arriving from the publisher/distributor. If my Subscriber database had been in honest-to-goodness Full Recovery Mode, I would have heard about it pretty quick, just like any other Full Recovery Mode situation where the transaction log is not being backed up. But because I wasn't in Full Recovery Mode, the log file was being truncated by the ongoing Checkpoints. Is that an accurate summary?

Thanks again.

Willem
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228445 Visits: 46342
Pretty much.

The DB was in full recovery, any query you run will tell you that, just in the absence of a full backup there's nothing to base log backups on and hence the log doesn't get retained, until someone takes that full backup (often without any idea of the consequences)

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


Willem Tilstra-440027
Willem Tilstra-440027
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 681
Gail, because I've only read about it and never actually run into the situation like this before, I've never really had a need to fully understand the issues surrounding changing to a Full Recovery mode from Simple. Your statement - I'm paraphrasing - if you're in full recovery mode and you've not yet made that first full backup, the transaction log backups have nothing as a base - were just what I needed to make it clear.

Thanks for your help on my issue and thanks for the education.

Willem
HARIANGEL
HARIANGEL
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 87
Hi,

I am having the same issue with my subscriber lately. However, My Subscriber is in simple recovery model. Still the log file is getting full. Even if try shrinking it is not shrinked. Please suggest me how to resolve this.

Thanks,
Haritha
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228445 Visits: 46342
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search