Microsoft SQL Server systems have four system databases:
The master database records all of the system-level information for a SQL Server system. It records all login accounts and all system configuration settings. The master database records the existence of all other databases and the location of the primary files that contain the initialization information for the user databases. The master database records the initialization information for SQL Server; always have a recent backup of master available.
The tempdb database holds all temporary tables and temporary stored procedures and fills any other temporary storage needs. The tempdb database is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. The tempdb database is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are automatically dropped on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.
The tempdb database grows automatically as needed. Each time the system is started, tempdb is reset to its default size. You can avoid the overhead of having tempdb grow automatically by using ALTER TABLE to increase the size of tempdb.
The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb, which uses the model database, is created every time SQL Server is started, the model database must always exist on a SQL Server system.
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and for recording operators.
In SQL Server version 7.0, every database, including the system databases, has its own set of files and does not share those files with other databases. The default location for these files is the C:\Mssql7\Data directory.
| Database file | Physical file name | Default size, typical setup |
|---|---|---|
| master primary data | Master.mdf | 7.5 MB |
| master log | Mastlog.ldf | 1.0 MB |
| tempdb primary data | Tempdb.mdf | 8.0 MB |
| tempdb log | Templog.ldf | 0.5 MB |
| model primary data | Model.mdf | 0.75 MB |
| model log | Modellog.ldf | 0.75 MB |
| msdb primary data | Msdbdata.mdf | 3.5 MB |
| msdb log | Msdblog.ldf | 0.75 MB |
In SQL Server 7.0, each database has its own set of files that can grow independently of the others.
Each database in Microsoft SQL Server contains system tables that record the data needed by the SQL Server components. The successful operation of SQL Server depends on the integrity of information in the system tables; therefore, Microsoft does not support users who directly update information in the system tables.
Microsoft provides a complete set of administrative tools that allow users to fully administer their system and manage all users and objects in a database. Users can use the administration utilities, such as SQL Server Enterprise Manager, to directly manage the system. Programmers can use the SQL-DMO API to include complete functionality for administering SQL Server in their applications. Programmers building Transact-SQL scripts and stored procedures can use the system stored procedures and Transact-SQL DDL statements to support all administrative functions in their systems.
An important function of SQL-DMO, system stored procedures, and DDL statements is to shield applications from changes in the system tables. Microsoft sometimes needs to change the system tables in later versions of SQL Server to support new functionality being added in that version. Applications issuing SELECT statements that directly reference system tables are frequently dependent on the earlier format of the system tables. Customers may not be able to upgrade to a later version of SQL Server until they have rewritten applications that are selecting data from system tables. Microsoft considers the system stored procedures, DDL, and SQL-DMO published interfaces, and seeks to maintain the backward compatibility of these interfaces.
Microsoft does not support triggers defined on the system tables because they may alter the operation of the system.
Another important tool for querying the SQL Server catalog is the set of information schema views. These views comply with the information schema defined in the SQL-92 standard. These views provide applications with a standards-based component for querying the SQL Server catalog.