Understanding System Databases

When we install SQL Server by default we will get 5 Databases.These can be seen under System Databases after you connect to SQL Server instance. The Default Databases are 

1. Master
2. Model
3. Msdb
4. Tempdb
5. Resourcedb

Resourcedb  is hidden database and it will not visible under system databases.

We will look at the functionality of each db individually.

Master : 

Master Database keeps the track of server installations and all other databases that are subsequently created.Master database has system catalogs that stores the information about system wide configurations such as 

•  endpoints
•  logins
•  Databases on current instance
•  Database files and usage
•  linked servers

Master database is critical to system.Operations such as creating another database,changing configuration values and modifying login accounts make modifications to Master database.

Model :

Model database is simply a template database, Every time we create a database SQL Server makes a copy of model to form the basis of the new database.

Msdb :

Msdb database is used by SQL Server Agent Service.Msdb stores the data related to following objects

• Jobs
• alerts
• log shipping
• policies
• database mail

Tempdb :

Tempdb is database is used as the work space. Tempdb is re created every time sql server restarted. It's used explicitly for temp tables created by users. Because Tempdb is re created, any objects that we create in it are lost the next time we restart the SQL Server instance.

Resourcedb :

The hidden mssqlsystemresourcedb is referred to as resource db. System objects such as system stored procedure and functions are stored here. Microsoft has created this database to allow fast and safe upgrades.