Sql Server System Databases

Sql Server has some system databases each serving a different purpose. When SQL Server is first installed, some system databases are installed by default. When we look at these system databases;

• master
• model
• msdb
• tempdb

Apart from these four databases, there are another two databases; one of them is called resource which is invisible, and distribution database which is installed when a replication is set up in SQL Server. These are also included in the system databases.

The following script is usually used to query the system databases.

select * from sys.databases where database_id < 4

Since system databases are automatically created when SQL Server is first installed, the first 4 rows are always reserved for system databases. distribution database is not included because replication is not installed by default. Therefore, on systems with replication installed, it is necessary to consider the distribution database when querying the system databases.

Now let’s see what these databases do;

master

The master database holds all system level information. System level metadata such as;

• user accounts,
• link servers,
• system configurations and
• endpoints
• location information where other databases are kept

are kept in this database.

Therefore, if the master database is inaccessible, SQL Server will not be able to start. That’s why it is very important to take regular backups of the system databases.

Database ID of master database is 1 and, physical path of the database can be queried by below script

select * from sys.master_files where database_id = 1

OR 

use [master]
go
select * from sys.database_files

model

The model database acts as a template for each database to be created on SQL Server. When a new database is to be created, the information like the database initial size, language settings and recovery model etc. is taken from the model database.

Database ID of model database is 3 and, physical path of the database can be queried by below script

select name, physical_name from sys.master_files where database_id = 3

OR

use [model]
go
select name, physical_name from sys.database_files

msdb

msdb database stores SQL Server Agent information, alerts, Jobs and their work history, and backup information.

This database also stores some features of Sql Server, such as Service Broker and Database Mail, which are also available in Sql Server.

Database ID of msdb database is 4 and, physical path of the database can be queried by below script

select name, physical_name from sys.master_files where database_id = 4

OR

use [msdb]
go
select name, physical_name from sys.database_files

tempdb

tempdb is one of the important databases used directly by database administrators and almost everyone who deals with software. tempdb is used in structures such as temporary user objects and internal objects created by the database engine;

Temporary user objects:

• local and global temp tables,
• temp stored procedures,
• temp variables and indexes created as temp

Internal Objects:

• Work tables created to hold the spool, cursor, sort and temporary large objects (LOB) that we see in the query plan,
• hash join and hash aggregate operations,
• In index creation and rebuild operations if SORT_IN_TEMPDB is selected as on,
• Operations such as GROUB BY, ORDER BY, UNOIN used in queries.
• Additionally, tempdb is used in operations such as data updates, online index operations, and AFTER trigger usage.

tempdb is both very critical and has a very important place for healthy and high-performance operation in heavily used systems. When it is not configured properly, it can cause serious performance problems. Therefore, configuring tempdb is one of the important issues. I will share how to configure tempdb in another article.

Database ID of tempdb database is 2 and, physical path of the database can be queried by below script.

select name, physical_name from sys.master_files where database_id = 2

OR 

use [tempdb]
go
select name, physical_name from sys.database_files

Tempdb is recreated every time SQL Server is restarted by taking the information from the model database. Unlike the other 3 databases, this database cannot be backed up.

Resource

Resource database is a read-only database that contains all SQL server system objects.

SQL Server system objects are physically kept in the resource database, but logically they appear under the sys schema under each database. Even though the resource database does not appear in SQL Server Management Studio or in the sys.databases table, you can see physical files named mssqlsystemresource.mdf and mssqlsystemresource.ldf under the C:\Program Files\Microsoft SQL Server\MSSQL<version>.instance_name>\MSSQL\Binn\ folder.

Database ID of resource database is 32767 and, physical path of the database can be queried by below script.

select name, filename
FROM
sys.sysaltfiles
WHERE dbid = 32767

distribution

As I mentioned before, the distribution database is not available on SQL Server by default. When a replication is set up, this database appears under system databases.

This database keeps all the metadata and historical data for all replication types and also keeps the transactions of transactional replication.

select name, physical_name from sys.master_files where database_id = DB_ID('distribution')

OR 

use [distribution]
go
select name, physical_name from sys.database_files

Hope this post will be helpful.

Feel free to contribute to make my writings get better and useful.