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 file increases abnormally after replication Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 2:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
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
Post #1386238
Posted Monday, November 19, 2012 10:37 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 7:39 AM
Points: 642, Visits: 520
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: www.markallen-online.co.uk
Business Website: www.weekendwebdesign.co.uk
Post #1386496
Posted Monday, November 19, 2012 11:31 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: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
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 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 #1386517
Posted Monday, November 19, 2012 11:32 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: Today @ 11:31 AM
Points: 42,844, Visits: 35,973
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 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 #1386518
Posted Monday, November 19, 2012 12:57 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 7:39 AM
Points: 642, Visits: 520
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: www.markallen-online.co.uk
Business Website: www.weekendwebdesign.co.uk
Post #1386546
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse