MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter












































Latest SQL Server Tips

SQL Server: Why it is Important to Customize Model Database Default Settings in SQL Server

Read Comments   |   Related Topics: SQL Server Management Studio Tips > Next Topic: SQL Server Disaster Recovery Tips


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 a DBA 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

SQL Server Model Database Data, Log, Auto Growth Default Settings

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.

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 Transactional Log file?
What are Virtual Log Files in SQL Server Transaction Log File?
How to Performance Transaction Log Backups in SQL Server?

Big 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 64 MB, unrestricted growth
  • Auto Growth for Log File:               By 64 MB, unrestricted growth to 2 TB

This is indeed a welcome change when compared to the Model system database default settings in the earlier versions of SQL Server.

Best Practice: Recommended Model System Database Default Settings in SQL Server

As per best practice we recommend the below changes to the Model System Database Default Settings across all version of SQL Server.

  • 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

We would also recommend you to evaluate the average data and log file size in your environment and then make necessary changes to model system database default settings for data, log and auto growth parameters. For more information, see How to Monitor SQL Server Database Transaction Log File Usage.

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);

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

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.

More... Disaster Recovery Tips for DBAs and Developers







Last Updated On: May 07, 2016



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: