Microsoft SQL Server/System databases

In this chapter you will learn the purpose of the system databases installed with every instance of Microsoft SQL Server.

Introduction

When you install Microsoft SQL Server, five system databases are automatically created on every SQL Server instance. These system databases allow the database engine and administrative applications to properly manage the system:

• Resource (SQL Server 2005 and higher only)
• master
• model
• msdb
• tempdb

Resource

The Resource database is a read-only database that contains all SQL Server system objects. This system database is not displayed in the SQL Server Management Studio object explorer, so users may not be aware of it. Interaction with the Resource database is indirect, through master database system views and functions that reference these objects. Because the Resource database is read-only, it is only modified by the system when a SQL Server hotfix, service pack, or upgrade is installed on an instance. The Resource database is installed only with SQL Server 2005 and higher.

master

The master database records the server-wide configuration information for a SQL Server instance, including all logins and database engine configuration defaults. The master database stores metadata including references to all other databases on the instance. This database records the initialization information for SQL Server, and an instance of SQL Server cannot start up if the master database is missing, corrupt, or otherwise unavailable. Prior to SQL Server 2005 all system objects were stored in the master database. Beginning with SQL Server 2005, the master database provides access to system objects through views, functions, and stored procedures that reference the read-only Resource database.

tempdb

The tempdb database is considered a global resource that all connections and all users can access. The tempdb database holds user-created temporary database objects, such as temporary tables and temporary stored procedures. This database is also used heavily by the database engine to serialize intermediate results and to hold other temporary data and objects that are generated during processing. Tempdb is always recreated from when the SQL Server service is first started. Because tempdb is so heavily used by the system, many system-wide performance optimizations are necessarily focused on making tempdb as efficient as possible.

model

The model database is a template for all newly-created databases on an instance. When a database is created it is initialized as a copy of the model database and then modified to the correct size and settings specified at creation time. Any objects in the model database are automatically copied to the new database. For this reason, some database administrators (DBAs) create functions or other database objects in the model database that they want to exist in all newly created databases.

Prior to SQL Server 2005 the database initialization process required allocating and wiping out ("zero-filling") all pages when a database was created. Beginning with SQL Server 2005 using the "instant initialization feature" improves performance by skipping the zero-fill when new data pages are allocated.

The tempdb database is recreated from a copy of the model database every time SQL Server is started, so the model database must always exist on a SQL Server system.

msdb

The msdb database is a system database used to store configuration and processing data and metadata for SQL Server features and applications including SQL Server Agent (job schedules and alerts), SQL Server Integration Services (ETL packages), Database Mail, and Service Broker.

Usage

Below is one request to get all the server databases by sizes:

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

Summary

SQL Server installs five system databases with every server instance: Resource (SQL Server 2005 and higher only), master, tempdb, model, and msdb. Each provides a specific set of system-wide functionality to the SQL Server instance, including access to server-wide configuration metadata, utility configuration and processing data, and the ability to create and utilize temporary objects.