During the installation of the Database Engine, the following system databases are generated:
- master
- model
- tempdb
- msdb
The following sections describe each of the system databases in turn.
master Database
The master database is the most important system database of the Database Engine. It comprises all system tables that are necessary for your work. For example, the master database contains information about all other databases managed by the Database Engine, system connections to clients, and user authorizations.
Because of the importance of this system database, you should always keep a current backup copy of it. Also, the master database is modified each time you perform an operation such as creating user databases or user tables. For this reason, you should back it up after the execution of such operations. (The section “Backing Up the master Database” in Chapter “Backup, Recovery, and System Availability” explains when it is necessary to back up the master database.)
model Database
The model database is used as a template when user-defined databases are created. It contains the subset of all system tables of the master database, which every user-defined database needs. The system administrator can change the properties of the model database to adapt it to the specific needs of their system.
tempdb Database
The tempdb database provides the storage space for temporary tables and other temporary objects that are needed. For example, the system stores intermediate results of the calculation of each complex expression in the tempdb database. The tempdb database is used by all the databases belonging to the entire system. Its content is destroyed every time the system is restarted.
The system stores three different elements in the tempdb database:
- User objects
- Internal objects
- Version store
Private and global temporary tables, which are created by users, are stored in the tempdb database. The other objects stored in this system database are table variables and table-valued functions. All user objects stored in tempdb are treated by the system in the same way as any other database object. This means that entries concerning a temporary object are stored in the system catalog and you can retrieve information about it using the sys.objects catalog view.
Internal objects are similar to user objects, except that they are not visible using catalog views or other tools to retrieve metadata. There are three types of internal objects: work files, work tables, and sort units. Work files are created when the system retrieves information using particular operators. Work tables are created by the system when certain operations, such as spooling and recovering databases and tables by the DBCC command, are executed. Finally, sort units are created when a sort operation is executed.
Optimistic concurrency (see Chapter “Concurrency Control”) uses the tempdb database as a place to store versions of rows. Hence, the tempdb database grows each time the system performs the following operations, among others:
- A trigger is executed
- An INSERT, UPDATE, or DELETE statement is executed in a database that allows snapshot isolation
msdb Database
The msdb database is used by the component called SQL Server Agent to schedule alerts and jobs. This system database contains task scheduling, exception handling, alert management, and system operator information; for example, the msdb database holds information for all the operators, such as e-mail addresses and pager numbers, and history information about all the backups and restore operations. For more information how this system database can be restored, see Chapter “Backup, Recovery, and System Availability”.
