Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system.
Newly created user databases use the same recovery model as the model database. The default is user configurable. To learn the current recovery model of the model, see View or Change the Recovery Model of a Database (SQL Server).
Important
If you modify the model database with user-specific template information, we recommend that you back up model. For more information, see Back Up and Restore of System Databases (SQL Server).
When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.
If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures. File properties of the model database are an exception, and are ignored except the initial size of the data file. The default initial size of the model database data and log file is 8 MB.
The following table lists initial configuration values of the model data and log files.
File | Logical name | Physical name | File growth |
---|---|---|---|
Primary data | modeldev | model.mdf | Autogrow by 64 MB until the disk is full. |
Log | modellog | modellog.ldf | Autogrow by 64 MB to a maximum of 2 terabytes. |
For SQL Server 2014, see model Database for default file growth values.
To move the model database or log files, see Move System Databases.
The following table lists the default value for each database option in the model database and whether the option can be modified. To view the current settings for these options, use the sys.databases catalog view.
Database option | Default value | Can be modified |
---|---|---|
ALLOW_SNAPSHOT_ISOLATION | OFF | Yes |
ANSI_NULL_DEFAULT | OFF | Yes |
ANSI_NULLS | OFF | Yes |
ANSI_PADDING | OFF | Yes |
ANSI_WARNINGS | OFF | Yes |
ARITHABORT | OFF | Yes |
AUTO_CLOSE | OFF | Yes |
AUTO_CREATE_STATISTICS | ON | Yes |
AUTO_SHRINK | OFF | Yes |
AUTO_UPDATE_STATISTICS | ON | Yes |
AUTO_UPDATE_STATISTICS_ASYNC | OFF | Yes |
CHANGE_TRACKING | OFF | No |
CONCAT_NULL_YIELDS_NULL | OFF | Yes |
CURSOR_CLOSE_ON_COMMIT | OFF | Yes |
CURSOR_DEFAULT | GLOBAL | Yes |
Database Availability Options | ONLINE MULTI_USER READ_WRITE |
No Yes Yes |
DATE_CORRELATION_OPTIMIZATION | OFF | Yes |
DB_CHAINING | OFF | No |
ENCRYPTION | OFF | No |
MIXED_PAGE_ALLOCATION | ON | No |
NUMERIC_ROUNDABORT | OFF | Yes |
PAGE_VERIFY | CHECKSUM | Yes |
PARAMETERIZATION | SIMPLE | Yes |
QUOTED_IDENTIFIER | OFF | Yes |
READ_COMMITTED_SNAPSHOT | OFF | Yes |
RECOVERY | Depends on SQL Server edition* | Yes |
RECURSIVE_TRIGGERS | OFF | Yes |
Service Broker Options | DISABLE_BROKER | No |
TRUSTWORTHY | OFF | No |
*To verify the current recovery model of the database, see View or Change the Recovery Model of a Database (SQL Server) or sys.databases (Transact-SQL).
For a description of these database options, see ALTER DATABASE (Transact-SQL).
The following operations cannot be performed on the model database:
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Design a Performant Data Model in Azure SQL Database with Azure Data Studio - Training
Learn how to create a data model, tables, indexes, constraints, and use data types with Azure data studio.
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.