SQL Server Model Database Facts

Problem

In this article, we are going to explore another system database – the model database and discuss five important facts about this SQL Server database.

Solution

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.

Conclusion

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.

Next Steps

For more information, please follow the links bellow:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-03-18

Five facts about the SQL Server Model Database