By: Artemakis Artemiou | Comments (3) | Related: More > System Databases
Problem
The Model database is one of SQL Server's system databases. It's purpose is to be used as the template for all new user defined databases created on a SQL Server instance. A question many of us might have is "how can we make the best use of the Model system database in order to help us during the process of creating new databases?"
The answer to the above question depends on the data strategy applied on each specific SQL Server instance.
Solution
During a user database's creation on a SQL Server instance, the new database's default settings are based on the settings of the Model system database. Even though modifying the Model database in order to make it a convenient user database template can become quite handy, prior to doing anything you need to take into consideration your data strategy and the purpose of the specific SQL Server instance. For example, you should ask yourself a few questions such as:
- What is your database growth plan?
- The answer can suggest the default Autogrowth settings for the database data and log files.
- What is your
backup strategy?
- The answer to this question can suggest the default value for the Recovery Model option.
- How ISO-compliant do you want to be?
- For example, the answer to this question determines if ANSI_NULLS should be set to ON as a default.
The above list of questions is just an example. There are even more questions to be asked and answered when it comes to the default database settings on a SQL Server instance (i.e. AUTO_SHRINK, QUOTED_IDENTIFIER, database owner, etc.). This of course, does not mean that a user database cannot be differentiated from the rest. In this tip, we are just talking about the default settings. These can be changed afterwards. Default settings, when properly configured to your needs, can save you valuable time during a new database's creation.
In the below example we see the creation of a new user database, along with using a non-modified Model database on a SQL Server 2014 instance, as well as the creation of another user database after we modified the Model system database's File settings for Autogrowth.
First let's check the current settings of the Model database in our SQL Server instance.
As you can see from the below two screenshots, the default settings are from the SQL Server installation are:
- Autogrowth for data file: By 1 MB, Unlimited
- Autogrowth for log file: By 10 percent, Unlimited
Figure 1 - Model Database Settings
Now let's create a user database and check its default Autogrowth settings:
Figure 2 - New User Defined Database Settings
As you can see from the above screenshot (Figure 2) the new user defined database we are creating (named "TestDB_Before_ModelDB_Change") uses the same Autogrowth settings as the Model database.
Now let's change the Autogrowth settings for the Model database.
The new settings are:
- Autogrowth for data file: By 64 MB, Unlimited
- Autogrowth for log file: By 32 MB, Unlimited
The below screenshot illustrates our change:
Figure 3 - Updated Model Database Settings
Let's create a new user defined database now and check its default Autogrowth settings:
Figure 4 - Updated New User Defined Database Settings
The above screenshot (Figure 4) shows us that the newly created user defined database sets as the new file Autogrowth default settings to match the Model database which are:
- Autogrowth for data file: By 64 MB, Unlimited
- Autogrowth for log file: By 32 MB, Unlimited
In SQL Server 2016 (currently RC3) you might have noticed that whenever you create a new database, the default Autogrowth settings for both data and log files is set to 64MB. This is achieved via the Model database, just like the above example. If you check the Model database in any SQL Server 2016 instance, just right after its installation, you will see that Autogrowth for both data and log files is set to 64MB.
This tip was an example on how you can use the Model system database in SQL Server, in order to set new default settings for every new user database that will be created on the instance. This of course does not mean that you cannot further modify database settings after their creation, but it does provide a handy way to avoid manually applying the same settings every time you create a new user database.
Important Note: Never change Model database's settings without first determining the required strategies for the specific SQL Server instance. Always be careful when modifying SQL Server system databases and generally any database settings.
Next Steps
Review the following tips and other resources:
About the author
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018).This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
