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, create users, stored procedures, tables, views, etc – whatever you do will be applied to any new databases.
The nice thing is that you can create a guaranteed set of users, stored procedures, and options (including collation options) by making changes to the model database. Once those changes are in place, they are applied to every new database.
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:
