SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

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 DataLog 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?

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

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

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

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement