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