Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Modifying the SQL Server Model System Database to Customize New Database Settings


By:   |   Read Comments (3)   |   Related Tips: 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
Model Database - Original Settings - Files Autogrowth

Figure 1 - Model Database Settings

Now let's create a user database and check its default Autogrowth settings:

User Database - General - Before Modifying Model

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:

Model Database - New Settings - Autogrowth

Figure 3 - Updated Model Database Settings

Let's create a new user defined database now and check its default Autogrowth settings:

New Database - Autogrowth - After Modifying Model DB

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:



Last Update:






About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server Architect, Software Developer, and a Microsoft Data Platform MVP (SQL Server).

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, July 05, 2016 - 6:42:16 AM - Kannan.C Back To Top

Dear Artemakis,

Thanks for the response. I also noticed that in system database (tempdb) it shows unlimited for LOG (i.e ldf). 

Regards,

 Kannan.C


Thursday, June 30, 2016 - 3:34:47 AM - Artemakis Artemiou Back To Top

Hi Kannan,

Thank you for your comment.

The reason you see 'Unlimited' in the screenshot is because the screenshot was taken before clicking on the 'OK' button. After you click 'OK', the maximum available log file size is specified which is 2,097,152 MB (2 TB).

This is not a bug, 2TB is just the maximum limit for a SQL Server log file size.

If you check the maximum capacity specifications for SQL Server (https://technet.microsoft.com/en-us/library/ms143432.aspx), you will see that the maximum log file size is indeed 2 TB. 

 

Regards,

Artemakis

 


Wednesday, June 29, 2016 - 8:21:46 AM - Kannan.C Back To Top

Not agreed. Log size for user created database will not be set to unlimited.Is it possible in SQL server 2012 to change log size to unlimited? Microsoft confirm that it is a bug and Log size will not be changed to unlimited instead they have provided 2TB is the limitation. But your screenshot shows unlimited. Can use please do this in sql server 2012 by your script?

Regards,

 Kannan.C


Learn more about SQL Server tools