Database Engine

What are the SQL Server System databases?

SQL Server System databases

SQL Server has 5 very default system databases which are

  1. Master
  2. MSDB
  3. Model
  4. TempDB
  5. Resource

Master

As the name implies this is one of the most important databases on the system and without it SQL Server will not start. It stores all the configuration of the instance and all other databases which are subsequently created. The information is stored into tables known as system tables.

MSDB

The MSDB database is used by SQL Agent and contains data about jobs, job schedules, alerts, log shipping, database mail, recovery of damaged pages….. This information is retrievable through the tables stored within this database.

Model

This is basically a template database for the creation of new databases.

TempDB

TempDB is recreated every time SQL is restarted and is basically used for storing of information temporarily when SQL Server doesn’t have sufficient memory available. This could be a sorting query, temporary tables, holding variables…..

Resource

This is a hidden read-only database which contains information on all objects within SQL server. If you look in the directory of your SQL Server installation you will find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf which contain the resource database. The hidden nature of the database makes it easy to upgrade via hot-fixes and service packs.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.