Tuesday, November 15, 2011

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