Why Model Database Default Settings Customization is Important for SQL Server?

What is a Model System Database?

Model is a system database which is used as a template while creating newer user databases in SQL Server.

Why you must make necessary changes to default settings of a Model System Database in SQL Server?

All the default settings of the Model database will be adopted by the newly created user databases. Hence, it is very important to make necessary customization to Model System Database on each instance of SQL Server.

In this tip, we will take a look at few of the very importance customizations which DBAs must do for a Model System Database so that all the newly created databases on the SQL Server Instance have better configuration that the default database settings.

Model Database Default Settings in SQL Server 2014 and Lower Versions

In SQL Server 2014, and lower versions the default settings for Data, Log and Auto Growth is set to lower values such as:

• Initial Size for Data File:        3 MB
• Initial Size for Log File:          1 MB
• Auto Growth for Data File:     By 1 MB, unrestricted growth
• Auto Growth for Log File:       By 10 Percent, unrestricted growth

When you compare these settings with the current size of Production SQL Server Database; then, you would know that these auto growth settings are really very bad. Smaller auto growth settings will lead to the creation of Large Number of Virtual Log Files in SQL Server. For more information, see Impact of Large number of Virtual Log Files in SQL Server Transaction Log File.

Steps to Change Database Data and Log File Settings in SQL Server are:-

1. Open SQL Server Management Studio and Connect to SQL Server Instance.
2. Expand Databases → Expand System Databases → Right Click Model database and click on Properties from the drop down menu.
3. In Select a Page Pane choose File and make the desired changes to the Initial Size and Growth Settings for Data and Log files and click OK to save the changes.
4. Once the above changes are made every new database which is created on the instance of SQL Server will be created with an Initial Size and Auto Growth Settings that of Model System Database.

Related Tips: Learn why lower auto growth settings can result in performance issues in SQL Server?

• How to Detect Virtual Log Files in SQL Server Transaction Log File?
• How to Fix Virtual Log Files in SQL Server Transaction Log file?
• What are Virtual Log Files in SQL Server Transaction Log File?
• How to Performance Transaction Log Backups in SQL Server?

Major Change: Model Database Default Settings in SQL Server 2016

Starting SQL Server 2016, by default the initial size of Data, Log file and Auto Growth is set to be as below.

• Initial Size for Data and Log File:  8 MB
• Auto Growth for Data File:            By 1024 MB, unrestricted growth
• Auto Growth for Log File:              By 512 MB, unrestricted growth to 2 TB

How to Change MODEL System Database Default Setting in SQL Server Using TSQL Script

We recommend you to make the necessary changes for SIZE and FILEGROWTH parameters and then run the below Transact-SQL (TSQL) script on the instance of SQL Server where you would like to ALTER the default settings of MODEL database.

USE [master];
GO

ALTER DATABASE [model] MODIFY FILE (NAME = N'modeldev', SIZE = 8 MB, FILEGROWTH = 1024 MB);
GO

ALTER DATABASE [model] MODIFY FILE (NAME = N'modellog', SIZE = 8 MB, FILEGROWTH = 512 MB);

Conclusion

In this tip we have seen why it is important to Customize Model Database Default Settings in SQL Server to achieve better performance from your SQL Server Database across Production and Non-Production environment. However, make it a point to document these changes which you make in your environment so that you can apply the same in case there is a need to Rebuild your environment from scratch in the event of a Disaster.

Trending SQL Server Disaster Recovery Articles and Tips for DBAs

• Different Ways to Retrieve SQL Server Configuration Details
• How to Move TempDB Database Files to a New Drive in SQL Server
• Improved Startup Parameters in SQL Server 2012
• Steps to Connect to SQL Server When all System Administrators are Locked Out
• How to Repair Suspect Database in SQL Server
• How to Connect to a Named Instance of SQL Server
• Introduction to SQL Server Configuration Manager
• SQL Server Disaster Recovery Tips for DBAs and Developers
• How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples