During a Twitter conversation today Malik Alamin (Twitter) asked about the Autogrow settings on his databases. The conversation naturally progressed to the question of how you change the default behavior and set all newly created databases to have user defined settings. Brent Ozar (Blog | Twitter) was able to point us in the right direction to find a solution. Modifying the model database.
About the model Database
When a new database is created either by using the CREATE DATABASE statement, that mean via SSMS as well, the model database is used as a template. This means that change made to the model database settings, including tables, permissions, stored procedures, and functions will all transfer over to the newly created database.
According to the TechNet article about the model database some file sizes may look different depending on what version of SQL Server is installed. Also, there are some things that cannot be modified in the model database. Items like CHANGE_TRACKING, ENCRYPTION, and TRUSTWORTY cannot be modified. A full list of restrictions can be found in the TechNet Article.
Example
I need to set the auto grow on my data file to Autogrow by 10% instead of 1 MB as was the default on my instance. I can run the ALTER DATABASE command or use SSMS against the model database
USE [master] GO ALTER DATABASE [model] MODIFY FILE ( NAME = N’modeldev’, FILEGROWTH = 10%) GO USE [master] GO ALTER DATABASE [model] MODIFY FILE ( NAME = N’modeldev’, FILEGROWTH = 10%) GO
Making these changes will alter all newly created databases to have the same settings.
Note: Jorge Segarra (Blog | Twitter) pointed out that this does NOT work in a SharePoint environment. SharePoint does not use the model database when it creates new site databases.
