3

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?

2
  • 1
    This is one for your DBA - by default the SQL backup will truncate but not shrink log files. the difference, the space is empty but reserved so as the log file fills up again its not getting fragmented. If its grown big for some reason (like not having a proper backup in place for some time) then you can shrink it with dbbc shrinkfile (but this shouldn't be an every time thing) techrepublic.com/blog/datacenter/…
    – Ryan
    Mar 1, 2012 at 8:29
  • 1
    Oh and #2 - until you actually do a successfull practice restore (to a VM?) then don't kid to yourself that you have a backup joelonsoftware.com/items/2009/12/14.html
    – Ryan
    Mar 1, 2012 at 8:32

3 Answers 3

2

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

1
  • Thanks for the MS guide, it looks like SharePoint needs a lot more care taken of its databases than I was aware. Could you possibly expand on point 2.
    – best
    Mar 1, 2012 at 18:21
1
  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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.