March 4, 2013 at 4:31 pm
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
March 4, 2013 at 5:53 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2013 at 4:18 pm
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
March 9, 2013 at 12:03 pm
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
March 11, 2013 at 9:46 am
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
March 31, 2013 at 11:47 pm
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
April 1, 2013 at 3:39 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply