Replication fails - transaction log full

Question

Replication fails - transaction log full

Cláudia Sousa Rego

SSCommitted

Points: 1780
More actions
May 30, 2006 at 3:41 am

#103171

Hello!!
In the past days, i dont know why, transaction log of the DB destination grows and gets full when i started replication...
This never happened..
Help me please.
How can I interpret the transaction log?
Thanks,
Cláudia
Regards,
Cláudia Rego
www.footballbesttips.com

Viewing 12 posts - 1 through 12 (of 12 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Cláudia

This is normal behaviour. Your destination database is having inserts, deletes and/or updates done on it and these operations will be recorded in the transaction log, causing it eventually to fill up. I don't know why it never happened before. Perhaps you have changed from simple recovery mode to full, or perhaps there are more transactions being replicated than there were before. You should create a job to truncate the transaction log regularly, and also consider increasing the size of the transaction log so that the truncations can be performed before the log fills up.

I'm not sure what you mean by interpreting the transaction log. If you want to read the transactions inside it then you need a third-party tool.

Hope that helps

John

Cláudia Sousa Rego SSCommitted Points: 1780 More actions · Answer 2

hummm...

I'm using snapshot replication... is there a limit for the size of the DB being replicated??

I think i dont change the recovery mode, but where i can see it?

I can read transaction log as a file text in EM but i would like understand it.

Thanks

Claudia

Regards,

Cláudia Rego

www.footballbesttips.com

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Ahhh - snapshot replication. While the snapshot is being applied, every insert will be written in the transaction log. That's why it keeps growing.

I think you're confusing the transaction log with the error log. You can't see the transaction log as text without a third-party utility. Search Books Online for Transaction Log since it's too big a topic for me to explain here.

You can see the recovery mode by looking at the Properties of the database - it's on the Options tab. If you change to Simple then you may stop the transaction log from filling up - but then you would lose the ability to do a point-in-time recovery. Given that this is a replica database that probably doesn't matter to you.

John

Cláudia Sousa Rego SSCommitted Points: 1780 More actions · Answer 4

John,

is there a way of deleting part of transaction log?

i have the idea that, if the transaction log is bigger than the data log, something is wrong. This is true?

Thanks

Claudia

Regards,

Cláudia Rego

www.footballbesttips.com

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Claudia

It's not necessarily a bad thing for the log to be bigger than the data - especially if you have a high volume of transactions. What you need to do is to truncate the transaction log. See the topic Truncating the Transaction Log in Books Online for information on this.

John

Cláudia Sousa Rego SSCommitted Points: 1780 More actions · Answer 6

Hello!

Is there some way to estimate ( calculate ) the growth in MB of the trasaction log after a replication??

Is there a way to say after the replication you will run a script that shrinks transaction log?

Thanks

Claudia

Regards,

Cláudia Rego

www.footballbesttips.com

homebrew01 SSC Guru Points: 55553 More actions · Answer 7

Claudia, did you determine what your recovery model is ? (full, simple or bulk-logged). Our subscription databases are simple because we don't need to log all the transactions that occur since it's mainly used for reading, and all the transaction are just from replication, which can be rebuilt in case of failure(s).

Cláudia Sousa Rego SSCommitted Points: 1780 More actions · Answer 8

I did not define a recovery model.

But yuour case is similar to mine because I really don't matter about transaction log... I would preffer if he did not exist

Regards,

Cláudia Rego

www.footballbesttips.com

homebrew01 SSC Guru Points: 55553 More actions · Answer 9

Then your recovery model is probably set to FULL, meaning you are logging all those transaction. You can change it to SIMPLE for just that database, provided you do not have a need for point-in-time recovery.

Cláudia Sousa Rego SSCommitted Points: 1780 More actions · Answer 10

I check it and is SIMPLE!!

Regards,

Cláudia Rego

www.footballbesttips.com

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

OK, it's possible that the snapshot process involves a large transaction that fills up your log before it gets committed. You need to expand your transaction log so that it can cope with this. Set it to as large as it needs to be and leave it - don't keep shrinking it.

John