Three SQL Server Settings for SharePoint Environments: Autogrowth Option, Auto Shrink Option, Shrink Database Task

As I was tuning a SharePoint environment today, I thought it would be a good opportunity to post a brief message about three SQL Server settings: Autogrowth, Auto Shrink, and the Shrink Database Task.

1. Autogrowth - Fixed Amount:

This setting applies to data files (.mdf) and transaction log files (.ldf). Autogrowth allows or prevents SQL Server from automatically increasing the size of the files when the need arises. In SharePoint environments, it is best to do capacity planning in advance and set the intial database sizes appropriately. Then, set the Autogrowth value for both the data files and the log files to a fixed amount. This ensures that the files grow in consistent increments. The fixed amount should be somewhat substantial, relative to the size of the database. This ensures that the growth does not occur too frequently.



2. Auto Shrink - Turn it Off:

Auto Shrink is an option that allows or prevents SQL Server from automatically reducing the size of database files. By default, the SQL Server Model database has the Auto Shrink option off. As Paul Randal points out, enabling Auto Shrink can lead to Auto-Grow, Auto-Shrink loops as well as database fragmentation, and other performance issues (Randal, 2007). As a general rule of thumb for most SharePoint environments, this option should remain off for system and content databases.




3. Shrink Database Task - Don't Schedule It:

The Shrink Database Task is an available Maintenance Plan Task. If set, this operation will reduce the size of a database according to a schedule, leaving a specified amount of free space. As with the Auto Shrink option, the Shrink Database Task leads to performance issues and is not recommend in SharePoint environments.

So, if an existing SharePoint environment does have a scheduled Shrink Database Task, then a suggested action would be to remove the Shrink Database Task and then plan to monitor and manage database size using a manual approach. When determining a plan for managing database size, it is important to keep Database Recovery Models in mind.

Reference

Randal, P. (August, 2008). Top Tips for Effective Database Maintenance. Retrieved July 30, 2010 from http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx?pr=blog.

Randal, P. (March, 2007). Turn AUTO_SHRINK off!! Retrieved July 30, 2010 from http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx.