Transaction log file increases abnormally after replication

Question

Transaction log file increases abnormally after replication

shohelr2003

Hall of Fame

Points: 3535
More actions
November 19, 2012 at 2:22 am

#265996

Dear,
1. I have implemented snapshot replication on my database. My database size is 2 GB. I scheduled snapshot agent to run twice a day.
2. After that I found my transaction log file growing abnormally. The next day from when I implemented replication, transaction log file was 14 GB. After that day, it was 24 GB. I was scared.
3. I took full database backup and tried to take log backup. An error message appeared like "no recent full database backup available".
4. Then I shrunk the log file but it couldn't shrunk. At last I disable replication and again tried for shrinking log file, then it worked.
Please help me to sort out the problem.
Regards,
Akbar

Viewing 5 posts - 1 through 4 (of 4 total)

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

WWDMark Hall of Fame Points: 3157 More actions · Answer 1

If you try 'DBCC OPENTRAN' it may indicate that your oldest active transaction is a replicated one which has been open for a while, this could potentially be your problem.

You may also need to run the log reader more often.

MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk

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

See this article, specifically the replication section. It links to a blog post on a bug in snapshot replication that causes exactly that behaviour.

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

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

VygonDBA (11/19/2012)
You may also need to run the log reader more often.

Snapshot replication does not use the log reader agent. That's transactional replication.

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

WWDMark Hall of Fame Points: 3157 More actions · Answer 4

GilaMonster (11/19/2012)
VygonDBA (11/19/2012)
You may also need to run the log reader more often.
Snapshot replication does not use the log reader agent. That's transactional replication.

Quite right, apologies - got carried away, 😀

MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk