model Database

 

Updated: October 2, 2015

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

System_CAPS_ICON_important.jpg 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 following table lists initial configuration values of the model data and log files. The sizes of these files can vary slightly for different editions of SQL Server.

FileLogical namePhysical nameFile growth
Primary datamodeldevmodel.mdfAutogrow by 10 percent until the disk is full.
Logmodellogmodellog.ldfAutogrow by 10 percent to a maximum of 2 terabytes.

To move the model database or log files, see Move System Databases.

Database Options

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 optionDefault valueCan be modified
ALLOW_SNAPSHOT_ISOLATIONOFFYes
ANSI_NULL_DEFAULTOFFYes
ANSI_NULLSOFFYes
ANSI_PADDINGOFFYes
ANSI_WARNINGSOFFYes
ARITHABORTOFFYes
AUTO_CLOSEOFFYes
AUTO_CREATE_STATISTICSONYes
AUTO_SHRINKOFFYes
AUTO_UPDATE_STATISTICSONYes
AUTO_UPDATE_STATISTICS_ASYNCOFFYes
CHANGE_TRACKINGOFFNo
CONCAT_NULL_YIELDS_NULLOFFYes
CURSOR_CLOSE_ON_COMMITOFFYes
CURSOR_DEFAULTGLOBALYes
Database Availability OptionsONLINE

MULTI_USER

READ_WRITE
No

Yes

Yes
DATE_CORRELATION_OPTIMIZATIONOFFYes
DB_CHAININGOFFNo
ENCRYPTIONOFFNo
NUMERIC_ROUNDABORTOFFYes
PAGE_VERIFYCHECKSUMYes
PARAMETERIZATIONSIMPLEYes
QUOTED_IDENTIFIEROFFYes
READ_COMMITTED_SNAPSHOTOFFYes
RECOVERYDepends on SQL Server edition1Yes
RECURSIVE_TRIGGERSOFFYes
Service Broker OptionsDISABLE_BROKERNo
TRUSTWORTHYOFFNo

1 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:

  • Adding files or filegroups.

  • Changing collation. The default collation is the server collation.

  • Changing the database owner. model is owned by sa.

  • Dropping the database.

  • Dropping the guest user from the database.

  • Enabling change data capture.

  • Participating in database mirroring.

  • Removing the primary filegroup, primary data file, or log file.

  • Renaming the database or primary filegroup.

  • Setting the database to OFFLINE.

  • Setting the primary filegroup to READ_ONLY.

  • Creating procedures, views, or triggers using the WITH ENCRYPTION option. The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.

System Databases

sys.databases (Transact-SQL)

sys.master_files (Transact-SQL)

Move Database Files

Show: