SQL Server includes the following system databases:-
- Master
- Model
- Msdb
- Tempdb
- Resource Database
Master:
The master database contains all of the system level information like logins, linked servers, endpoints, and other system-wide configuration settings.
The master database is also stores information about the other databases on this instance and the location of their files.
If the master database is not present, SQL Server cannot start via Configuration Manager.
model:
The model database is used as a template whenever a new user database is created.
You can change most database properties like, collation, recovery model, create users, stored procedures, tables, views, etc – whatever changes made to model database are do applied to any new databases.
msdb:
The msdb system database is used by the SQL Server Agent, database mail, Service Broker, etc.
One important item is that msdb holds backup history.
Important Tables:
msdb.dbo.backupset
determine when each database and filegroup was last backed up.
Resource:
The resource database is a hidden system database.
Database id of resource database is 32767.
In resource database system objects are stored. The data file location of resource database is C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Binn.
tempdb:
Is used for holding temporary objects, query processing and sorting purposes.
Tempdb is re-created every time the SQL Server service is started.
By default tempdb starts at 8MB in size.
Any object created starting with #table will be stored into tempdb database.
It is best practice to have trace flags enabled for autogrow of tempdb and upto 8 tempdb log files to speed up execution of queries that uses temporary objects.
Ref: