Transaction log file increases abnormally after replication

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Transaction log file increases abnormally...

Transaction log file increases abnormally after replication

Rate Topic

Display Mode

Topic Options

Author

Message

shohelr2003

Posted Monday, November 19, 2012 2:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 122, Visits: 323

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

WWDMark

Posted Monday, November 19, 2012 10:37 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:59 AM
Points: 448, Visits: 351

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
Website: www.weekendwebdesign.co.uk

Post #1386496

GilaMonster

Posted Monday, November 19, 2012 11:31 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 37,675, Visits: 29,930

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

GilaMonster

Posted Monday, November 19, 2012 11:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 37,675, Visits: 29,930

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

WWDMark

Posted Monday, November 19, 2012 12:57 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:59 AM
Points: 448, Visits: 351

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, BigGrin

MCITP
MCTS - E-Business Card
Twitter: WWDMark

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

email: info@weekendwebdesign.co.uk
Website: www.weekendwebdesign.co.uk

Post #1386546

« Prev Topic | Next Topic »

Permissions