SharePoint 2010 SQL AutoGrowth Settings

Scenario

• You would like your SharePoint 2010 and Reporting Services (SSRS) databases to perform well by avoiding excessive database auto-growth and disk fragmentation. Note that this is in addition to other SQL performance tuning steps such as splitting up the tempdb across physical files and optimizing SQL Server disk access speeds.

Solution

Note: The following settings are initial baselines.  You should adjust the Initial Size and Autogrowth settings appropriately.  Monitoring and reporting on auto-growth is vital to this process.

Also, make sure you have a SQL Server Maintenance plan in-place to backup your transaction logs every 5 to 60 minutes.  If you don't do this, you must do full backups from SQL Server, not SharePoint to avoid using up all your disk space.  The interval chosen for you transaction log backups will be the maximum data loss interval in-case of failure.

•  Change Initial Size and Autogrowth Settings
• Right click the database in SQL Server Management Studio
• Click Properties
• Select Files, change as in the table below, and click OK
• If blank, leave as-is or set to the auto growth setting

Database	File	Initial Size	Autogrowth
master	Data	10MB	10MB
	Log		10MB
tempdb	Data	20MB	20MB
	Log		10MB
spfarm_AdminContentDB	Data	500MB	100MB
	Log		10%
spfarm_BusinessDataCatalogDB	Data	10MB	10MB
	Log		10MB
spfarm_ConfigDB	Data	250MB	100MB
	Log	500MB	50MB
spfarm_EnterpriseSearch	Data	50MB	20MB
	Log		20MB
spfarm_EnterpriseSearch_CrawlStore	Data	250MB	250MB
	Log		50MB
spfarm_EnterpriseSearch_PropertyStore	Data	50MB	20MB
	Log		20MB
spfarm_MetaDataDB	Data	50MB	20MB
	Log		20MB
spfarm_MySites_Content	Data	250MB	250MB
	Log		100MB
spfarm_PerformancePointDB	Data	50MB	50MB
	Log		20MB
spfarm_ProfileDB	Data	100MB	50MB
	Log		20MB
spfarm_SecureStoreDB	Data	10MB	10MB
	Log		10MB
spfarm_SocialDB	Data	10MB	50MB
	Log		30MB
spfarm_StateServiceDB	Data	100MB	20MB
	Log		20MB
spfarm_SyncDB	Data	100MB	20MB
	Log		20MB
spfarm_UsageAndHealthDB	Data	150MB	50MB
	Log		20MB
spfarm_WebAnalyticsReportingDB	Data	20MB	20MB
	Log		20MB
spfarm_WebAnalyticsStagingDB	Data	20MB	20MB
	Log		20MB
spfarm_WordAutomationDB	Data	20MB	20MB
	Log		20MB
spfarm_WWW_Root_Content	Data	1,000MB	500MB
	Log	100MB	100MB
ReportSerer	Data	50MB	50MB
	Log		20MB
ReportServerTempDB	Data	20MB	20MB
	Log		10MB