Confluence_log size keeps growing under MYSQL

[MedImpact]

The Confluence_log.ldf size under MYSQL keeps growing and is using way to much space for what it is.  We have expanded that drive a few times already so please suggest what we need to do to fix this issue?

[Davin Studer]

There are some things to consider with regards to the transaction log (.ldf). One thing you need to ask yourself is if you ended up losing data out of Confluence what is the largest block that would be ok? Now, the goal is to not lose data, but if you did what wouldn't sink you? If your answer is that you could be ok if you lost some data then that will determine the recovery model you set for MS SQL.

If you are ok with only being able to go back to the most recent database backup you could set the recovery model to simple and shrink your log. In this scenario all the transactions are removed from the log as soon as they are committed to the database. In this scenario your log will grow to a certain point, but will pretty much stop there under normal circumstances. A typical scenario for people who use the simple recovery model is to do a weekly full backup of the database, and a daily incremental off the full.

However, if you recovery model is set to full (which yours probably is) all the transactions will stay in the log until you back it up. In this case the log will just grow and grow until you back up your database ... which means you probably aren't backing it up. The nice thing about the full recovery mode is that since all the transactions are retained you should be able to restore your database up to any arbitrary point. But you need to make sure to backup the database and the transaction log in this scenario ... not just the database. A typical scenario for people who use the full recovery model is to do a weekly full backup of the database, a daily incremental off the full, and transaction log backups every X minutes.

With any backup strategy is is important to make sure the backups are backed up to a remote server. Don't rely on having your backups on the machine where the database exists, since the typical reason you would need a backup is because the actual machine has been hosed in some way and you don't want you backups there as they might get hosed as well.

[AnnWorley]

Hi Zain,

All the documentation I found indicates that the .ldf file extension is an MS SQL log. Is it possible you are using MS SQL?

There is no setting in Confluence to reduce the logging in MS SQL but there is a way to shrink the log and change the recovery mode in MS SQL so that the log doesn't write as many entries. Please see this discussion for pros and cons of the approach: LDF file growing too big.

Of course it is up to your DBAs, but it seems like if you have a Production Backup Strategy in place you would not need the logs that allow you to roll back to a point in time in order to recover Confluence.

Thanks,

Ann

[MedImpact]

Thanks for the reply. How do I set the backup to run monthly instead of daily?

How do I disable Production Backup Strategy?

[AnnWorley]

Hi Zain,

A Production Backup Strategy, as documented, basically consists of backing up your Confluence Home Directory and database. Since you would use your file system back-ups and database tools to do these, I would not know how you should stop them.

If you mean the automatic XML back-ups you may reschedule these by editing the Scheduled Job that is called Back up Confluence, as described in Scheduled Jobs.

Changing the XML back-ups will not shrink the .ldf file for MS SQL or prevent it from growing in the future. The database administration options discussed in  LDF file growing too big can help, if your DBAs feel the changes are appropriate. Here is another article about the database procedures that would reduce it: How to reduce/shrink LDF file size in SQL Server using SQL Management Studio

Thanks,

Ann