System Databases in SQL Server 2005

Microsoft SQL Server 2005 uses five system databases:

master – uses master.mdf for data and masterlog.ldf for logging
model – uses model.mdf for data and modellog.ldf for logging
msdb – uses msdbdata.mdf for data and msdblog.ldf for logging
resource – uses mssqlsystemresource.mdf for data and mssqlsystemresource.ldf for logging
tempdb – uses tempdb.mdf for data and templog.ldf for logging



The master Database

The master database stores all the system-level information for SQL Server. The data stored by the master database includes information for: configuration settings, logon accounts, linked servers and endpoints, user database file locations, and properties.

Due to the nature of the data stored, SQL Server cannot operate without the master database. So it is a very good idea to backup this database after changing the SQL Server configuration, modifying, adding, or removing any databases.

The model Database

SQL Server uses the model database for creating new databases. When the “create database” statement is used, SQL Server copies the contents of the model database to the newly created database. If there are any common database objects that would prove useful in all databases created by SQL Server, it is possible to add those objects to the model database. Then when a database is created by the SQL Server instance, the user defined objects will be copied to it along with the default objects. Since SQL Server recreates the tempdb database every time it is started, the model database is required in order for SQL Server to start.

The msdb Database

The msdb database is used by SQL Server to store information on operations performed by SQL Server. This includes information for: the SQL Server Agent, Database Mail, the Service Broker, log shipping, backup, and maintenance plan job parameters.

The resource Database

The resource system database was introduced with SQL Server 2005. This database is used for storing all the system views and stored procedures. Logically, each SQL Server database will contain all these system objects, however, they are physically stored within the resource database. The resource database is read-only and does not include any user data.

In previous versions of SQL Server, the system objects were stored in the master database. The motivation behind moving the objects to a separate database is to make updating the SQL Server more efficient. Improvements and fix-ups to the SQL Server system generally manifest mostly on the system objects. A separate database to store the system objects reduces the number of files that need to be replaced with an update.

The tempdb Database

As the name implies, SQL Server uses the tempdb database for storing temporary data and data objects. The tempdb database is used when an operation requires a temporary table, stored procedure, or other database object to be performed. Intermediary data for large sort operations is also stored in the tempdb database as well as temporary data for internal SQL Server operations.

Every time SQL Server is restarted, the tempdb system database is recreated thus clearing any temporary data stored during the last SQL Server session. In cases where a high volume of users and operations are performed with SQL Server the tempdb database can grow to use a significantly large amount of disk space. It is important to plan accordingly in these scenarios since running out of disk space where the tempdb database is stored will have catastrophic effects on the operation of SQL Server.


SOME OF THE ADDITIONAL DATABASES ADDED

Master

Purpose – Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
Prominent Functionality
Per instance configurations
Databases residing on the instance
Files for each database
Logins
Linked\Remote servers
Endpoints
Additional Information
The first database in the SQL Server startup process
In SQL Server 2005, needs to reside in the same directory as the Resource database

Resource

Purpose – The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
Prominent Functionality
System object definition
Additional Information
Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
Prior to SQL Server 2005 the system related data was stored in the master database
Read-only database that is not accessible via the SQL Server 2005 tool set
The database ID for the Resource database is 32767
The Resource database does not have an entry in master.sys.databases

TempDB

Purpose – Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
Prominent Functionality
Manage temporary objects listed in the purpose above
Additional Information
Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state

Model

Purpose – Template database for all user defined databases
Prominent Functionality
Objects
Columns
Users
Additional Information
User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
The database configurations such as the recovery model for the Model database are applied to future user defined databases

MSDB

Purpose – Primary database to manage the SQL Server Agent configurations
Prominent Functionality
SQL Server Agent Jobs, Operators and Alerts
DTS Package storage in SQL Server 7.0 and 2000
SSIS Package storage in SQL Server 2005
Additional Information
Provides some of the configurations for the SQL Server Agent service
For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database

Distribution

Purpose – Primary data to support SQL Server replication
Prominent Functionality
Database responsible for the replication meta data
Supports the data for transaction replication between the publisher and subscriber(s)

ReportServer

Purpose – Primary database for Reporting Services to store the meta data and object definitions
Prominent Functionality
Reports security
Job schedules and running jobs
Report notifications
Report execution history

ReportServerTempDB

Purpose – Temporary storage for Reporting Services
Prominent Functionality
Session information
Cache