Five facts about the SQL Server Model Database


By:   |   Updated: 2021-03-18   |   Comments   |   Related: 1 | 2 | More > System Databases


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".

model database properties

In the "Files" page, we can see the model database file related information.

model database properties

Now, we will change the sizes of the data and log files, and autogrowth options, then click "OK".

model database properties

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.

model database properties

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:

model database properties

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.

model database properties

After that, if we open the New Database creation window, we can see that all these changes are applied for a new database.

model database properties

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.

object explorer

Now, if we create a new database (NewDB2), we can see that the above objects are also created in this new database.

run stored proc

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.

tempdb objects

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.

tempdb objects

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.

sql server error

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.

sql server error log

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:



Last Updated: 2021-03-18


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips



Comments For This Article





download





Recommended Reading

Five Facts about the SQL Server Resource Database

Restoring the SQL Server Master Database Even Without a Backup

Track SQL Server TempDB Space Usage

Tempdb size resets after a SQL Server service restart

SQL Server Backup and Restore History Queries














get free sql tips
agree to terms