DID YOU KNOW…. When we create a database using “Create Database <database-name>” Database properties are not inherited from Model Database.

DID YOU KNOW…. When we create a database using “Create Database <database-name>” Database properties are not inherited from Model Database.

  • Comments 1

When we create the database using the Management Studio GUI, the size of the new database is the same as the Model Database and also the properties of the new database such as MAXFILESIZE, FILEGROWTH is inherited from the model database.

 

However when we create a database using following TSQL command without specifying any of the database properties, the default size of the database files and properties are not same as that of Model database

Create Database <Database-Name>;

 

When you run the Create Database TSQL Command without any file properties parameters, all the file properties of the Model Databases are ignored except the initial size of the Data file. These ignored values of Maxsize, Growth for both datafile and logfile are hardcoded in the code and is by design.

 

In the below example only the Size property has been changed.

The default setting of Model is below (sp_helpfile)

name         fileid  filename                             filegroup   size   maxsize   growth  usage
---------- ------  ---------------------------------------------------------------------------
modeldev   1     C:\Program Files (x86)\     PRIMARY 1216 KB Unlimited 1024 KB  data only                     

                            Microsoft SQL  Server\MSSQL.1\MSSQL\DATA\model.mdf

modellog    2     C:\Program Files (x86)\     NULL         512 KB Unlimited  10%         log only
                            Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf NULL

Now you run the create database statement as below in the SSMS Query Window
Create Database MSTEST

If you run the sp_helpfile you would see the below changes. You will see that the

name       fileid filename                           filegroup   size           maxsize   growth        usage
---------- ------  ---------------------------------------------------------------------------

MSTEST     1   C:\Program Files (x86)\    PRIMARY 1216 KB Unlimited 1024 KB data only
                        Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSTEST.mdf

 

MSTEST_log 2 C:\Program Files (x86)\    NULL    504 KB 2147483648 KB  10% log only
                         Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSTEST_log.LDF

The create database statement without the file properties would create the database with only the Data file size equivalent to the Model Datafile. However the transaction log size is computed as 25% of the Data files size. If the database is created using the GUI, then both the data and log file properties are derived from the Model database and the Create Database statement is constructed. Running a profiler will show you the Create Database statement will have the file properties similar to Model Database.

Below is another example where the default size of Model is changed and the CREATE Database TSQL is run.
The behavior of GUI does not change.

 

For the Model Database, Data file size is changed to 20 MB and Log file is changed to 100 MB

name         fileid  filename                 filegroup   size   maxsize   growth  usage
---------- ------  ---------------------------------------------------------------------------
modeldev   1   C:\Program Files (x86)\   PRIMARY 20480 KB Unlimited 1024 KB  data only                     

                         Microsoft SQL  Server\MSSQL.1\MSSQL\DATA\model.mdf

modellog 2     C:\Program Files (x86)\    PRIMARY 102400 KB Unlimited 10%    log only
                         Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf NULL

New database

name       fileid filename                   filegroup   size   maxsize   growth  usage
---------- ------  ---------------------------------------------------------------------------

MSTEST        1   C:\Program Files (x86)\  PRIMARY 20480 KB Unlimited 1024 KB data only
                            Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSTEST.mdf

MSTEST_log 2   C:\Program Files (x86)\    NULL       5120 KB  2147483648 KB  10% log only
                            Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSTEST_log.LDF

Here you see that the data file has been created with the same size as the Model Data file but the Log file is 25% of Data file.

 


 

Satya Madhuri Krovvidi & Levi Justus
Microsoft SQL Server

Reviewed By Pranab Majumdar, SQL Server Escalation Services

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post