System Databases and Data

Microsoft® SQL Server™ systems have four system databases:

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 earlier versions of SQL Server, the master and model system databases were on a single file, known as the master device. The first 2 MB allocation of tempdb also resided on the master device, and sometimes the pubs sample database. The restriction of having these databases all reside in a single file sometimes caused problems with space in the master and model databases. In SQL Server 7.0, all of these databases have their own set of files that can grow independently of each other.

Each database in SQL Server 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 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.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.