very large SharePoint Database Log files

It is not unusual that you may find sharepoint databases have very large log files, sometimes they can be hundreds times of size of data file. The cause of this is:

• the databases in "Full" recovery model, and
• the log files have never been backed up ( this happens when doing sharepoint native backup, it only backups data, not log)

When databases in a Full recovery model, neither full nor differential backup will truncate inactive segments in log files. Those segments thus can't be reused in the future, which causes the log file to continue growing without bound.

The solution is,

• turn recovery model to 'Simple' (for non-prod environment only), or
• specifically backup Log files by using the following sql statement:
• BACKUP LOG WSS_Content_80 TO DISK='z:\temp\80.log.bak'. The result of this is: when log backup is done, sql server automatically truncates all inactive transactions and allow those segments to be reused in the future.\
• do Transaction Log back up on database. The T-log backup will automatically truncate log.

However, the log file size will not be reduced until you shrink the log file size by sql statement:

• DBCC SHRINKFILE ('Log_Logic_Name')

you can find the log logical name by issue sp_helpdb or from UI.

So to summary, the best practice is

• Turn on "Simple" recovery model for Dev, and Keep "Full" model for Prod
• do sql backup rather than sharepoint backup
• full backup complemented by daily partial backup and then by hourly T-log backup