Five facts about the SQL Server Model Database
In this article, we are going to explore another system database – the model database and discuss five important facts about this SQL Server database.
As its name suggests, the model database is used as a model for creating a new database in the SQL Server instance. This means that when we create a new database, this new database is formed by copying the model database (the rest of the new database is empty). So, let's highlight some important features of the model database below.
Fact 1 – Initial size of the files for a new database are inherited from the model database
The initial database file sizes and autogrowth options of a new database are the same as the model database. Let's explore this with an example.
In the SQL Server Management Studio (SSMS), we right-click on the model database and choose "Properties".
In the "Files" page, we can see the model database file related information.
Now, we will change the sizes of the data and log files, and autogrowth options, then click "OK".
After that, if open the New Database creation window, we can see that the default sizes of the files and autogrowth options are the same as what was set for the model database.
So, new database files will default to the sizes and autogrowth options from the model database.
Fact 2 – The recovery model of a new database is the same as the model database's recovery model
The recovery model of a new database is also inherited from the model database. In the example below, we can see that the model database is in the Full recovery model:
We will change the recovery model from Full to Bulk-logged. Also, we will change the compatibility level and Auto Shrink options. Then, we click "OK" to save these changes.
After that, if we open the New Database creation window, we can see that all these changes are applied for a new database.
Thus, as we can see, while creating a new database, the database recovery model and other database options are copied from the model database.
Fact 3 – The database objects created in the model database will exist in a newly created database
If we create database objects in the model database, these objects will appear in any new database in that instance. Let's try to test this feature. In SSMS, we run the following code to create a table in the model database.
USE model GO CREATE TABLE TestTable(ID INT, Val INT) GO INSERT INTO TestTable(ID, Val) VALUES(1,100) GO
In the code below, we create a stored procedure in the model database.
USE model GO CREATE PROCEDURE getTestTableData AS BEGIN SELECT * FROM TestTable END
If we expand "Tables" and "Stored Procedures" in SSMS, we can see the created objects.
Now, if we create a new database (NewDB2), we can see that the above objects are also created in this new database.
For example, if we execute the stored procedure in the new, just created database, it successfully runs.
USE [NewDB2] GO DECLARE @return_value int EXEC @return_value = [dbo].[getTestTableData] SELECT 'Return Value' = @return_value GO
This feature of the model database can be used to create a predefined template for a new database. For instance, if it is required to have some special objects in all new database, these objects can be just added to the model database, instead of creating them every time after a new database creation. In these cases, it is recommended to have a backup of the model database in order to restore the modified version of the model database in case of database issues.
Fact 4 – The objects created in the model database appear in TempDB as well
Not only a new database inherits objects created in the model database, but these also exist in TempDB when it gets created.
First, we can see that in our instance, there are no user-defined tables and stored procedures in TempDB.
As we have already added a table and stored procedure in the model database, let's just restart the instance. After the restart, we can see under "Tables" and "Stored Procedures" the objects that were defined in the model database.
This is explained by the fact, that every time the SQL Server instance is started, the TempDB database is recreated. Therefore, as a newly created database, it inherits the objects created in the model database.
Fact 5 – The model database must always exist on SQL Server
It is impossible to start a SQL Server instance if the model database is not available. This is because when the instance starts, it recreates the TempDB database and the model database is necessary for that to occur.
Let's reproduce this situation. We stop the SQL Server and move the model database data files to another location. Then, if we try to start the instance, we will get the following error message.
If we look at the ERRORLOG file, we can see that the reason for SQL Server failing to start is the unavailability of the model database.
Hence, the model database is mandatory in every SQL Server instance.
To sum up, the model database is used as a template for creating new databases in the instance. This means that a newly created database in the instance inherits the model database's options, recovery model, data file initial size, and so on. It is also possible to create database objects, such as tables, in the model database and these objects will appear in any new database that is created in the instance. The TempDB database also inherits some of the settings of the model database and therefore the model database must exist for SQL Server to start.
For more information, please follow the links bellow:
- SQL Server Model Database Tutorial
- Replace Model SQL Server Database with a User Database
- How to Restore Model Database in SQL Server
- Modifying the SQL Server Model System Database to Customize New Database Settings
- System Databases
- Model Database
Last Updated: 2021-03-18
About the author
View all my tips