SharePoint Database Log File Cancer

SharePoint Database Log File Cancer

The database log files can grow like cancer, consuming large amounts of disk storage and impacting performance if not properly managed. This can be a particular problem for smaller deployments without dedicated DBAs or database experienced local administrators.

The underlying issue is that native SharePoint backups do not backup the log files. Content databases are created with FULL recovery model if the System Model database has the FULL recovery model specified. This results in unbounded log file growth. It is not unusual for log files to grow to several times the size of the data files before someone notices the problem or worse yet, the log file consumes all available space such that the database server no longer responds.

Sample Scenario:

Assume one or more of the content databases are in FULL recovery model. Further assume only database backups have been performed; i.e., no explicit log backups, therefore the log file has never been truncated. Executing  sp_helpdb WSS_Content_Litware_Internet_Site might show the following results.

Now perform a BACKUP DATABASE either a native SharePoint backup or using the BACKUP statement in a SQL Server query window.

BACKUP DATABASE WSS_Content_Litware_Internet_Site TO DISK='z:\backups\WSS_Content_Litware_Internet_Site.bak'

Processed 11488 pages for database 'WSS_Content_Litware_Internet_Site', file 'WSS_Content_Litware_Internet_Site' on file 1.

Processed 2 pages for database 'WSS_Content_Litware_Internet_Site', file 'WSS_Content_Litware_Internet_Site_log' on file 1.

BACKUP DATABASE successfully processed 11490 pages in 13.162 seconds (7.150 MB/sec).

Execute sp_helpdb again, and notice the log file size is still the same.

Now perform a BACKUP LOG in a SQL Server query window. A log backup logically truncates the log file, marking as inactive the virtual logs that do not hold any part of the logical log.

BACKUP LOG WSS_Content_Litware_Internet_Site TO DISK='z:\backups\WSS_Content_Litware_Internet_Site_Log.bak'

Processed 9 pages for database 'WSS_Content_Litware_Internet_Site', file 'WSS_Content_Litware_Internet_Site_log' on file 1.

BACKUP LOG successfully processed 9 pages in 2.669 seconds (0.026 MB/sec).

Execute sp_helpdb one more time, and notice the log file size is still the same, because we have not shrunk it to free the truncated virtual logs. We need execute DBCC SHRINKFILE to physically free the truncated virtual logs.

DBCC SHRINKFILE ('WSS_Content_Litware_Internet_Site_log')

Executing  Sp_helpdb now shows a reduction in log file size:

We have now recovered 88 MB of space by physically shrinking the log file.

Note that the SHRINKFILE was only needed because periodic log backups had not been performed as part of the routing backup plan. Periodic log file backups would have prevented the log file from growing so much in the first place.

Summary:

SharePoint native backups, either through the Central Administration > Operations > Backup and Restore page or the stsadm.exe command line backup operation, DO NOT manage the log files. If the SharePoint database is in FULL recovery model, you must explicitly backup, and optionally shrink the log files, or the log files will consume every increasing amounts of valuable disk or SAN storage. Unnecessarily large log files will also impact the database performance.

Talk to your DBA staff about setting up recurring log file backups. Depending upon the frequency of database updates, log files may need to recur from a few times per day, to as often as every 10 minutes.

Also, if you plan to implement log shipping for disaster recovery, frequent log file backups will be an absolute necessity!