model Database
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.
model Usage
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.
Physical Properties of model
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.
File |
Logical name |
Physical name |
File growth |
|---|---|---|---|
Primary data |
modeldev |
model.mdf |
Autogrow by 10 percent until the disk is full. |
Log |
modellog |
modellog.ldf |
Autogrow by 10 percent to a maximum of 2 terabytes. |
To move the model database or log files, see Moving 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 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 |
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 edition1 |
Yes |
RECURSIVE_TRIGGERS |
OFF |
Yes |
Service Broker Options |
DISABLE_BROKER |
No |
TRUSTWORTHY |
OFF |
No |
1 To verify the current recovery model of the database, see How to: View or Change the Recovery Model of a Database (SQL Server Management Studio) or sys.databases (Transact-SQL).
For a description of these database options, see ALTER DATABASE (Transact-SQL).
Restrictions
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 dbo.
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 database or 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.