master database

Definition of master database in The Network Encyclopedia.

Sponsor: Geeky T-Shirt: No, I Will Not Fix Your Computer (Amazon)

What is Master Database?

In Microsoft SQL Server, the database that stores the system-level configuration information about the server running SQL Server and about each database on the server. This information includes system tables, transaction logs, system configuration settings, login accounts, and database location and initialization information.

The master database is one of four system databases on every server running SQL Server. The others are as follows:

  • The msdb database:
    Used by the SQL Server Agent for job scheduling and recording purposes.

     

  • The model database:
    Used as a template for creating new user databases when the CREATE DATABASE statement is run.

     

  • The tempdb database:
    Stores all temporary tables, work tables, and temporary stored procedures generated during operation of the server running SQL Server. The contents of tempdb are lost when connections with the server are terminated or the system is shut down, and the database is re-created upon system startup to provide a clean work area.

     

NOTE

In SQL Server version 7, all databases, including the four system databases, are separate files in the \MSSQL\Data directory on the server. Each database consists of a database (.mdf) file and a corresponding log (.ldf) file. This differs from SQL Server 6.5, in which the master and model databases and the first 2 MB of the tempdb database exist as a single file called the master device.

TIP

Be sure to always have a recent backup copy of the master database for recovery from system failure on a server running SQL Server. Don’t bother backing up the tempdb database, but for improved system performance you can prevent tempdb from automatically growing to meet system needs by increasing its default size to its maximum anticipated size.