System Databases and Data

SQL Server Architecture

previous page next page
SQL Server Architecture

System Databases and Data

Microsoft® SQL Server™ 2000 systems have four system databases:

  • master

    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. master is the database that records the existence of all other databases, including the location of the database files. master records the initialization information for SQL Server; always have a recent backup of master available.

  • tempdb

    tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb 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 dropped automatically 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.

    By default, tempdb autogrows as needed while SQL Server is running. Unlike other databases, however, it is reset to its initial size each time the database engine is started. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.

  • model

    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 is created every time SQL Server is started, the model database must always exist on a SQL Server system.

  • msdb

    The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.

In SQL Server 2000 and 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.

Database file Physical file name Default size, typical setup
master primary data Master.mdf 11.0 MB
master log Mastlog.ldf 1.25 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 12.0 MB
msdb log Msdblog.ldf 2.25 MB

The sizes of these files may vary slightly for different editions of SQL Server 2000. For more information about default locations of these files, see Directories and File Locations.

Each database in SQL Server 2000 contains system tables recording 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 directly updating the 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 data definition languare (DDL) statements is to shield applications from changes in the system tables. Microsoft sometimes needs to change the system tables in new 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 old format of the system tables. Sites may not be able to upgrade to a new version of SQL Server until they have rewritten applications that are selecting 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; 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 a standards-based component for querying the SQL Server catalog.

You should not code Transact-SQL statements that directly query the system tables unless that is the only way to obtain the information required by the application. In most cases applications should obtain catalog and system information from:

  • The SQL-92 Information Schema Views.

  • SQL-DMO.

  • The catalog functions, methods, attributes, or properties of the data API used in the application, such as ADO, OLE DB, or ODBC.

  • Transact-SQL system stored procedures, catalog statements, and built-in functions.

previous page start next page