Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Carat
Yak Posting Veteran
92 Posts |
 Posted - 2009-04-04 : 07:20:35
|
| I got an sms this morning because the transaction log of one of our databases was full. I didn't understand this because we take every night a Full back and every hour a Transaction log backup and so normally after every Transaction log back the transaction log will be truncated. I tried taking another Full backup but I saw that the transaction log was not truncated. The backup was larger than before, from 20GB to 33GB.I looked in sys.databases to the field log_reuse_wait and the value is 'Replication'. We have several publications on this databases. We use transactional replication and transactional replication with updatable subscriptions.What can I do to resolve this problem? I enlarged the transaction log so that the jobs can succeed but this is just a temporary solution.Thanks! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-04 : 11:02:54
|
| Then backup log more frequently like every 15 mins. |
 |
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2009-04-04 : 11:37:20
|
| I already tried to backup the database but the transaction log won't truncate. So taking backups more frequently won't solve this problem. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2009-04-09 : 07:29:41
|
| We take a full backup daily and Transaction log backups every hour. This is our strategy for the past 3 years and we never had any problems while our database grow 5GB over 3 years. Interesting article, thanks!There was a problem with the Log Reader agent. I restarted it and than all the transactions were sent to the subscriber and everything was ok. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-04-09 : 15:56:42
|
| If the log is waiting on replication, then the logreader agent needs to run. Then you need to backup the transaction log. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-04-09 : 16:56:50
|
| Until Log reader is done VLF's cannot be truncated. What is Polling interval for Log Reader Agnt. |
|
|
|
|
|
|
Transaction log full: log_reuse_wait = replication