Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction log in Subscriber database for Transactional Replication Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 4:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 65, Visits: 594
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
Post #1426513
Posted Monday, March 4, 2013 5:53 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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...

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 2008, MVP
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

Post #1426544
Posted Friday, March 8, 2013 4:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 65, Visits: 594
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
Post #1428810
Posted Saturday, March 9, 2013 12:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1428922
Posted Monday, March 11, 2013 9:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 65, Visits: 594
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
Post #1429354
Posted Sunday, March 31, 2013 11:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 14, 2014 6:20 AM
Points: 5, 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
Post #1437358
Posted Monday, April 1, 2013 3:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1437384
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse