As a SharePoint professional, there are some configuration options you should know about in order to ensure good performance in your SharePoint 2013 deployment. One of these configurations is the Autogrowth size for content databases (DBs). In this article I will show you how to configure this property in order to apply the same value for all SharePoint content DBs.
The problem with the default value for the Autogrowth property in SQL Server
SQL Server defines a default Autogrowth value for every DB that is created in a SQL instance:
- 1 MB for the DB file.
- 10% for DB Log file.
This value is also applied to SharePoint Content DBs, which can be a problem in terms of performance as you can read in the TechNet recommendation regarding to the right value for the autogrowth property (Note: The SharePoint 2010 reference is also quite useful) :
- The default settings for a new database are to grow by 1 MB increments. Because this default setting for autogrowth results in increases in the size of the database, do not rely on the default setting. Instead, use the guidance provided in Set SQL Server options .
- Set autogrowth values to a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be.
- Consider the following guidelines when configuring autogrowth:
- When you plan content databases that exceed the recommended size (200 GB), set the database autogrowth value to a fixed number of megabytes instead of to a percentage. This will reduce the frequency with which SQL Server increases the size of a file. Increasing file size is a blocking operation that involves filling the new space with empty pages.
- Set the autogrowth value for the Search service application Property Store database to 10 percent.
- If the calculated size of the content database is not expected to reach the recommended maximum size of 200 GB within the next year, set it to the maximum size the database is predicted to reach within a year — with 20 percent additional margin for error — by using the ALTER DATABASE MAXSIZE property. Periodically review this setting to make sure it is still an appropriate value based on past growth rates.
- Maintain a level of at least 25 percent available space across disks to allow for growth and peak usage patterns. If you are managing growth by adding disks to a RAID array or allocating more storage, monitor disk size closely to avoid running out of space.
Therefore, I strongly recommend changing the default value for the Autogrowth property and following Microsoft guidelines for SharePoint Content Databases.
Changing autogrowth configuration through the System Databases
First option to change the default value for the Autogrowth property is by changing this value in the “model” DB you can find under the “System Databases” folders in your SQL Server instance:
- Run SQL Server Management Studio in your CloudShare environment.
- In the object explorer, expand the Databases folder and then the System Databases folder. Select the “model” DB and open the “Properties” window.
- Change the default 1 MB value for the Autogrowth property to a suitable one and save the changes.
- If you try to create a new DB in your SQL Server instance, you will see how the new value for the Autogrowth property is immediately applied.
Let’s see what happens when we create a new SharePoint 2013 content DB:
- Navigate to the SharePoint 2013 Central Administration. In the Application Management section, click the “Manage content databases” link.
- In the “Content Databases” page, click the “Add a content Database” link to create a new content DB.
- Configure the required settings to create a new content DB through the “Add Content Database” page.
Note: You can also create a new content DB by using the New-SPContentDatabase cmdlet:
New-SPContentDatabase -Name -WebApplication
- If you review the Autogrowth property value for the content DB you will see the values configured in the “model” DB are not being applied independently; you create the DB through the SharePoint 2013 Central Administration or PowerShell.
Changing autogrowth configuration through T-SQL
Since the “model” DB technique didn’t work to apply a custom value for the Autogrowth property, you can always try to use some T-SQL in order to change the default value for this property:
- The ALTER DATABASE T-SQL command allows you to modify an existing DB. If you use the MODIFY FILE option, you can specify a value for the associated DB files using the following syntax:
ALTER DATABASE WSS_Content_80_2
MODIFY FILE (NAME=WSS_Content_80_2,FILEGROWTH=20MB);
- After you execute the command above, you will see how the defined value for Autogrowth property has been applied to the Content DB.
And that’s all about how to use the same autogrowth property value for SharePoint Content DBs. Happy CloudSharing!