Compartilhar via


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.

Important

This feature has changed from earlier versions of SQL Server. For more information, see Breaking Changes to Database Engine Features in SQL Server 2005.

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

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

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

NUMERIC_ROUNDABORT

OFF

Yes

PAGE_VERIFY

CHECKSUM

Yes

PARAMETERIZATION

SIMPLE

Yes

QUOTED_IDENTIFIER

OFF

Yes

READ_COMMITTED_SNAPSHOT

OFF

Yes

RECOVERY

FULL

Yes

RECURSIVE_TRIGGERS

OFF

Yes

Service Broker Options

DISABLE_BROKER

No

TRUSTWORTHY

OFF

No

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

See Also

Concepts

Considerations for Backing Up the model and msdb Databases
Considerations for Restoring the model and msdb Databases
System Databases

Other Resources

sys.databases (Transact-SQL)
sys.master_files (Transact-SQL)
Moving Database Files

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 November 2008

Changed content:
  • Added restriction on using WITH ENCRYPTION.

5 December 2005

Changed content:
  • Removed reference to the SUPPLEMENTAL_LOGGING option.