What are the system databases on MSSQL and their purpose

Microsoft SQL Server uses 6 databases for operating:

  1. master
  2. resource
  3. model
  4. msdb
  5. distribution
  6. tempdb

Applies to:

  • Microsoft SQL Server 2005

Files

Database.mdf file.ldf file
mastermaster.mdfmastlog.ldf
resourcemysqlsystemresource.mdfmssqlsystemresource.ldf
modelmodel.mdfmodellog.ldf
msdbmsdbdata.mdfmsdblog.mdf
distributiondistmdl.mdfdistmdl.ldf
tempdbtempdb.mdftemplog.ldf

If you have not change the default install, the files are located in: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Master database

The master database contains system wide information:

  1. Logins
  2. Linked servers
  3. Server configuration
  4. User database names and locations
  • MSSQL will NOT start without the master database.

Resource database

The resource database contains all the system objects:

  1. System stored procedures
  2. System views
  • You cannot see the resource database with the SQL Server Management Studio.
  • You cannot see the resource database either with systems views or system procedures [ie: sp_helpdb].

Model database

The model database is a template that MSSQL uses to create the user databases. Meaning that each object you add to the model database will be replicated in each NEW user databases.

MSDB database

The msdb database stores the information for:

  1. SQL Server Agent
  2. Service Broker
  3. Log shipping
  4. Execution history

Distribution database

The distribution database is used for replication either as a replication server or a replication client.

  1. Replication metadata as a replication server or a replication client.
  2. Stores the actual transactions to be replicated.

Tempdb database

The tempdb database stores all the temporary data and all the temporary objects:

  1. Temporary tables. When the table name starts with # such as [ie: #abc], that table will be stored in tempdb.
  2. Temporary stored procedures. When the procedure name starts with # such as [ie: #abc], that procedure will be stored in tempdb.
  3. The tempdb file is emptied during the shutdown of the SQL Server.
  4. If the tempdb.mdf does not exist at startup, it will be recreated at the startup.
  5. Stores cursor data.
  6. Stores row versions when using row versioning or snapshot isolation.
  7. Store temporary during large sort, large indexing operation.