Santosh Chandavaram, SQL Baba
Notes on SQL Server Administration, Maintenance and Development
Notes on Microsoft SQL Server System Databases: Master DB, Model DB, MS DB, Temp DB
What are SQL Server System Databases?
System databases are auto created when SQL Server is installed and there are 4 major types of system databases:
- Master DB
- MSDB
- Model DB
- Temp DB
Less known auto generated system databases are:
- Distribution DB
- MS SQL System Resource
Each of the above databases is used for certain purposes.
What is Master DB?
Master DB as says is truly a “master” database. It keeps the SQL Server running. Master DB keeps track of information about all the system and user defined databases hosted on that particular instance of Microsoft SQL Server. Simply put, Master DB is where most of the metadata of that SQL Server Instance is processed. Good thing is that this metadata in Master DB can be accessed for obtaining useful information especially regarding performance of that particular instance of SQL Server.
It is highly suggested to back up Master DB before and after a major change to the instance.
What is MSDB?
MSDB is the job keeper and runner. MSDB holds metadata about the SQL Agent Jobs, SSIS jobs, Service Broker, and DB Mail.
What is Model DB?
Model DB serves as a default template for all databases that will be created on SQL Server. If changes are made to this Model DB, all the new databases that are created from then on that instance are also created along with those changes.
What is Temp DB?
Temp DB, on a very high level acts as a connection agent for all the users connecting to the databases on that particular instance. Temp DB holds temporary objects while a connection is active and these objects are removed when the
connection goes offline.
Take a look at Narayana Vyas’s post on this topic for more information.
