Enormous database log files - Why are they not being truncated after a SharePoint backup runs?

Question

I've performed a full Farm backup and also a full Site Collection backup using Central Administration site but there are still several enormous database log files

• 4GB - SharePoint_AdminContent
• 53GB - SharePoint_ConfigDB
• 58GB - WebAnalyticsServiceApplication_ReportingDB
• 58GB - WSS_Content_log

The WSS_Content data file is only 33GB! We perform a site backup nightly via a PowerShell script and Task Scheduler.

1) Other than setting the recover model to SIMPLE how can I reduce the size of the log files?

2) More importantly are the backups I'm producing actually backing up all of the data I need?

Answer 1

Even though SharePoint databases are "not to touched" (i.e. changing schema, adding records directly etc.), that does not mean database maintenance best practices do not apply like they do for any other databsae. For instance, without regular maintenance, SP DB's will become highly defragmented etc. MS has a white paper describing these best practices for DB maintenance, which can be found here:

http://www.microsoft.com/download/en/details.aspx?id=24282

Answer 2

1. The logs you are talking about are SQL logs. If you are doing a SharePoint site backup instead of a SQL backup I wouldn't expect the logs to shrink. I think the only solution for that is a full SQL backup - Someone correct me if I'm wrong though. This is simply my understanding of how it would work.

2. In my experience the site backups are hit or miss. It's nice because they can be significantly more granular than you would normally get from a SQL backup but I've found they generally aren't ideal. To quote Microsoft:

Note that you may lose some customizations or settings in the process. For example, the backup file does not include workflows, alerts, and properties stored at the site collection level. The backup file also does not include the Recycle Bin state or objects inside it.

Answer 3

If you use the SIMPLE recovery model in SQL Server you just have the backups. If you lose your system the most recent restore point is your last backup. In many cases that is good enough, but it depends how frequently your content is changing and the cost if changes since the last backup were lost.

If you use the FULL recovery model you can restore to your last backup, then restore your transaction log backups (if any), and then restore the "tail of the log" potentially right up to the time of the failure. That comes at a price - the transaction logs have to be retained, and if you don't back them up they just grow and grow.

So you need to have a SQL Server backup schedule if you are going to use FULL recovery. If the log files are huge, once you have done the first backup you will need to shrink the database the one time to get it to a manageable size.

The config db is particularly prone to this problem because there are a lot of transactions even though this database is usually quite small, so the log file gets very big if you leave it on FULL recovery (the default) and don't back it up.

A SharePoint farm backup gets most things (assuming the backup works), but you should have a copy of your SharePoint root directory (14 hive) and the web root directory, to capture things like web.config modifications and artefacts that weren't deployed through WSPs.