Hi Friends,
Many improvements and new features are there in the SQL Server 2016. One of that change is about the model database default settings in SQL Server 2016. We know that the model database plays an important role just like a template while we are creating a new database over that instance. By default our new database contains all the settings of the model database, like recovery model, file size, file growth etc.
One of the most important setting is file growth. Which is better, growth in terms of fixed size (in MB or GB) or growth in percentage? For log files, It’s always better to have all the VLFs of same size rather than multiple VLFs of different size. If we wants, all of our VLFs in the same size, then we always change the growth setting from the default 10% to a specific value in MB or GB (depends on the behavior of transactions or workload).
If you are selecting the growth in terms of percentage. Let say, the data file size is small, then 10 percent will be small. Think about the situation when your data file size is 512 GB or 1 TB then 10 percent will not be small. You can see in the below image, that data file growth setting in SQL Server 2014 is 1 MB. Which is very small and can lead to a large number of auto growth events. These are the cases where people are using the default settings and doesn’t pre-allocate the space to database files.
In SQL Server 2016, Microsoft has made changes to model database default file size and their growth setting as mention in below images (one from SQL Server 2014 and another from SQL Server 2016):
SQL Server 2014: Model database properties
SQL Server 2016: Model database properties
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
