SQL Server System Databases – Basic Concepts

Introduction

The SQL Server design implies a one-to-many mapping between the database engine (instance) and the databases hosted on the instance. It means that you can deploy several databases on one instance of the SQL server. According to the Microsoft documentation, you can have up to 32767 databases on a single instance of SQL Server. Of course, there will be limitations, like the resources on the server, managing concurrency on TempDB, network traffic, etc.

Databases deployed on a SQL Server instance can either be System Databases or User Databases. System Databases come installed with the instance. In this article, we will discuss the purpose of each System database. Also, we’ll clarify what you need to care for when managing system databases on SQL Server.

Overview of System Databases

System databases are a part of many processes taking place when you install an instance of SQL Server. By default, these databases are created in the following paths:

 %programfiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA 

and 

%programfiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log 

The path can be different. During the installation of SQL Server, you can specify the location of the system database files. 

To list all the system databases in an instance, you can invoke the code in Listing 1. Listing 2 can be used to determine the location of the datafiles associated with the system databases. Note that in both scripts, we use a filter returning databases with database_id of 5 or higher than 5.

The essential “visible” system databases have database_ids 1,2,3,4 – they refer to master, tempdb, model, and msdb, respectively. There is also an “invisible” database called the resource databases and other system databases created when you enable features like replication.

-- Listing 1: System Databases
select 
name
,database_id
,create_date
,state_desc
,recovery_model_desc
,log_reuse_wait_desc
,physical_database_name
,is_db_chaining_on
,is_broker_enabled
,is_mixed_page_allocation_on
from sys.databases 
where database_id<5;

-- Listing 2: System Database Files
select 
name
,database_id
,DB_NAME(database_id)
,name
,physical_name
,type_desc
from sys.master_files 
where database_id<5;

Figure 1: System Databases

The master Database

The master database is the first database open on the start of SQL Server, containing the following data: 

• The records of the structure/configuration of the instance and all other databases. 
• The most dynamic management views that are necessary for monitoring the instance. 

Thus, it has the information necessary for opening all other databases. That’s why it has to be first to open. The question is how to do it. 

The SQL Server startup parameters contain two entries, which define the locations of the master database data and log files. The default startup parameters include only three lines – the third one is the error log file location. When SQL Server starts up, it must be able to write to that error log file. 

The master database opens first. The information stored in the master database, including the configurations defined using sp_configure, applies to open other databases and complete the instance startup process.

Figure 3: SQL Server Startup Parameters

There are several ways to learn about useful SQL Server system objects, like Dynamic Management Views and Functions. 

For instance, expand the views or programmability nodes for the master database on object explorer. There, review these objects’ names and get more details from Books Online. 

You can also migrate logins from one instance to another. For that, restore a backup of the master database to the destination instance. We’ll describe the specific technique in a separate article.

Figure 4: Navigating Master Database Objects

The tempDB Database

The tempDB database is responsible for the storage of temporary data like intermediate results of heavy operations. It could contain index rebuilds, row versions of databases configured with isolation levels, and other features that depend on row versioning, such as READ_COMMITTED_SNAPSHOT. 

Most people would consider tempDB a potential performance bottleneck, as every user database’ s functions in an instance depend on tempDB. 

Tuning tempDB should be a deliberate exercise. However, starting with SQL Server 2016, you can setup tempDB accurately during the installation of SQL Server. A previous article describes in detail how you should configure and monitor tempDB. 

The model Database

You can treat the model database as a template. Every user database created in an SQL Server instance takes up the configuration of the model database. It has the same size and autogrowth settings, same file locations, etc. However, you can specify those settings deliberately in a different way in the CREATE DATABASE statement. 

In essence, if you have an application creating a database on its own with default settings, you may want to configure the model database. It ensures you to have some control over these automatically created databases’ turning out. 

Let’s experiment with this a little, using the code in Listing 3.

-- Listing 3: Using the Model Database
select 
name
,database_id
,DB_NAME(database_id) database_name
,name
,physical_name
,type_desc
,size/1024*8 [size (MB)]
,growth/1024*8 [size (MB)]
from sys.master_files 
where DB_NAME(database_id)='model' or DB_NAME(database_id) like 'EXP%';
GO

create database EXP_BEFORE;
GO

USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 102400KB , FILEGROWTH = 131072KB )
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', SIZE = 102400KB )
GO

create database EXP_AFTER;
GO

select 
name
,database_id
,DB_NAME(database_id) database_name
,name
,physical_name
,type_desc
,size/1024*8 [size (MB)]
,growth/1024*8 [size (MB)]
from sys.master_files 
where DB_NAME(database_id)='model' or DB_NAME(database_id) like 'EXP%';
GO

The code performs the following steps:

1. Query the sys.master_files to determine the settings used in the model database
2. Create a database 
3. Change the model database file growth and autogrowth settings
4. Create another database
5. Query sys.master_files to again

Pay attention to the single phrase for the database creation – “CREATE DATABASE.” This way, it picks up the settings belonging to the model database. The results show that the second time we issued the CREATE DATABASE command, the EXP_AFTER database acquired a file size of 96GB and autogrowth of 128 MB (see Figure 5).

Figure 5: Using the Model Database

It is also worth mentioning that not all database settings are automatically picked up from the model database. For example, MIXED_PAGE_ALLOCATION set to OFF when you create a new database, even though it is ON in model and other system databases.

The msdb Database

You may consider the msdb to be an SQL Server Agent database. It stores all data related to jobs, database mail, operators, and automation. Data related to transaction log shipping, replication, etc., are also there. You can verify that by running sets of queries in Listing 4 in your instance. For instance, objects related to automation are present in the msdb.

-- Listing 4: Querying msdb Objects

-- Jobs, Alerts, Operators sit in the msdb database ...
select * from msdb..sysjobs
select * from msdb..sysalerts
select * from msdb..sysoperators
select * from msdb..sysmail_allitems

-- ... NOT in the master database
select * from master..sysjobs
select * from master..sysalerts
select * from master..sysoperators
select * from master..sysmail_allitems

You can learn a lot about objects in msdb databases by extracting and investigating them on SQL Server Books Online. The approach is the same as with the master database. 

You can migrate jobs, operators, etc. from one SQL Server instance to another. For that, take the backup of the msdb database from the source instance and restore it to the destination instance. We shall address this in another article.

Epilogue

In this article, we have provided a quick overview of system databases. 

System databases come with a SQL Server installation by default. Understanding various system databases and their roles is an excellent aid for managing your SQL Server instance. 

You can get more information on what they store and understand how to manage them from Books Online and other articles on CodingSight.

References

1. System Databases
2. MS SQL Server Databases & Storage
3. Cross Database Ownership Chaining