System Databases

The system databases exist to support the operation of SQL Server. The four system databases?master, model, msdb, and tempdb?are created by the installation program. The system databases are said to contain "metadata," or data about data. These databases cannot be dropped.


As the name implies, the master database is the repository for all system-wide information for SQL Server. If an object is defined at the server level, it is stored in master. Login accounts, configuration settings, system-stored procedures, and the existence of other databases are recorded in the master database. SQL Server will not run if the master database is damaged or corrupt; therefore, it is imperative that the master database is backed up on a regular basis. See Chapter 16, "Database Backup and Restore," for further information.


The msdb database holds information for the SQL Server Agent. When you define jobs, operators, and alerts, they are stored in the msdb database. Information about backup operations is also stored in msdb, so it is important to back up msdb even if you are not using the SQL Agent. In SQL Server Enterprise Edition, tables exist to support the log shipping feature.


The model database is a template on which all user-created databases are based. All databases must contain a base set of objects known as the database catalog. When a new database is created, the model is copied to populate the requisite objects. Conveniently, objects can be added to the model database. For example, if you want a certain table created in all your databases, create the table in the model database and it will be propagated to all subsequently created databases. If you do modify the model database, remember to back it up or your changes will be lost.


tempdb holds temporary objects in SQL Server. Explicitly created temporary tables and temporary stored procedures, as well as system-created temporary objects, share tempdb. Think of tempdb as a workspace, or scratch pad, that SQL Server uses to hold interim data. A large sort operation, for instance, is performed in tempdb before being returned to the user process. Index creation can be set to use tempdb as well. Having tempdb on a separate disk system can vastly improve index creation time. As SQL Server re-creates tempdb each time SQL Server is started, you don't need to back up tempdb.


The distribution database, although technically a system database, is not created by default. If you choose to set up replication, the distribution database will be installed. See Chapter 22, "Data Replication," for more information.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features