SQL Server: Why it is Important to Customize Model Database Default Settings in SQL Server
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
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....
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
- 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
Last Updated On: May 07, 2016
Please leave your Valuable Comment or Let us know how this article helped you: