SQL Server: System Databases

During the installation of the Database Engine, the following system databases are generated:

  • master
  • model
  • tempdb
  • msdb

NOTE There is another, “hidden” system database, called the resource database, which is used to store system objects, such as system stored procedures and functions. The content of this database is generally used for system upgrades.

The following sections describe each of the system databases in turn.

1. master Database

The master database is the most important system database of the Database Engine. It comprises all system tables that are necessary for your work. For example, the master database contains information about all other databases managed by the Database Engine, system connections to clients, and user authorizations.

Because of the importance of this system database, you should always keep a current backup copy of it. Also, the master database is modified each time you perform an operation such as creating user databases or user tables. For this reason, you should back it up after the execution of such operations. (The section “Backing Up the master Database” in Chapter 16 explains when it is necessary to back up the master database.)

2. model Database

The model database is used as a template when user-defined databases are created. It contains the subset of all system tables of the master database, which every user-defined database needs. The system administrator can change the properties of the model database to adapt it to the specific needs of their system.

NOTE Because the model database is used as a model each time you create a new database, you can extend it with certain database objects and/or permissions. After that, all new databases will inherit the new properties. Use the ALTER DATABASE statement to extend or modify the model database, the same way as you modify user databases.

3. tempdb Database

The tempdb system database is a shared database for all users of the Database Engine instance. It provides, among other things, the storage space for temporary tables and other temporary objects that are needed. Its content is destroyed every time the system is restarted.

NOTE I will describe the tempdb system database in more detail than the other system databases because it can be a significant factor in relation both to performance and to the management of the whole Database Engine instance to which it belongs.

This section covers the following topics in relation to the tempdb database:

  • The use of tempdb
  • Differences between tempdb and other databases
  • Specification of size and number of files for tempdb
  • Editing information concerning tempdb

The following subsections describe these features.

3.1. Use of the tempdb Database

Three types of objects are stored in the tempdb system database:

  • Temporary user objects, such as temporary tables and stored procedures
  • Internal objects
  • Version store (see the “Row Versioning” section in Chapter 13)

Local and global temporary tables as well as temporary stored procedures, all of which are created by users, are stored in the tempdb database. The other objects stored in this system database are table variables and table-valued functions. All user objects stored in tempdb are treated by the system in the same way as any other database object. This means that entries concerning a temporary object are stored in the system catalog and you can retrieve information about the object using the sys.objects catalog view.

Internal objects are similar to user objects, except that they are not visible using catalog views or other tools to retrieve metadata. There are three types of internal objects:

  • Work files Created when the system retrieves information using particular operators
  • Work tables Created by the system when certain operations, such as spooling and recovering databases and tables by using the DBCC commands, are executed
  • Sort units Created when a sort operation is executed

The third use of the tempdb system database is for storing row versions when using the READ COMMITTED SNAPSHOT level or SNAPSHOT ISOLATION level with user tables. (For the detailed description of these isolation levels, see Chapter 13.) Both forms of isolation level mentioned use the feature called optimistic concurrency to save older versions of rows as updates are made, so you can see the prior committed values. During this process, the tempdb database grows each time the system performs the following operations, among others:

  • An INSERT, UPDATE, or DELETE statement is executed in a snapshot transaction.
  • A trigger is executed.

NOTE Because of optimistic concurrency, the system makes heavy use of the tempdb database. For this reason, make sure that tempdb is large enough and monitor its space regularly. (Monitoring the space of tempdb is described at the end of this section.)

3.2. Differences Between tempdb and Other Databases

The most important difference between tempdb and other databases is that tempdb is re-created every time the corresponding Database Engine instance starts up. (You can use this fact to quickly determine the last time a Database Engine instance was restarted, checking the create_date column of the tempdb system database.)

Another difference is a caching behavior. To reduce the impact on structures of the tempdb database, the system can cache temporary objects in the same way as caching is done for persistent database objects. Instead of dropping a temporary object, the system keeps the system metadata and truncates the table data. The process of truncation reduces the storage requirement to a single data page and reduces the allocation information to a single Index Allocation Map (IAM) page. (An IAM page tracks the pages/extents allocation for a specific allocation unit of a table.) The main benefit of caching is that it avoids almost all of the allocation and metadata costs of re-creating the temporary object.

There is also a difference in the logging process that is done for tempdb. The tempdb system database works in simple recovery mode, but only the minimal amount of information concerning modification operations is stored in the transaction log. (Simple recovery mode will be described in the Chapter 16 section “Recovery Models”)

3.3. Specification of Size and Number of Files for tempdb

Two important issues concerning tempdb are its total size and the number of files used to store data in it. There is no simple way to specify the size of tempdb. The best way is an empirical one: Use your system for a couple of days or weeks and monitor how large the tempdb database is. Generally, you should allow tempdb to auto-grow. For monitoring the size of your tempdb database, use the sys.dm_db_file_space_usage dynamic management view (see the upcoming Example 15.2).

During the installation process, on the Database Engine Configuration page of the installation wizard (see Chapter 2), you can specify the number of tempdb files on the TempDB tab. The default value is 4. There are several important recommendations for specifying the number of files. First, you should create a data file per logical processor for your tempdb, with a maximum of eight data files. Second, each of the multiple data files should have the same size so the Database Engine can apply the proportional fill optimization procedure. Third, according to Microsoft, you should set the Autogrowth property for tempdb.

3.4. Editing Information Concerning tempdb

You can edit information of temporary objects (tables and stored procedures) in the tempdb database the same way you would do for any other such objects. Example 15.1 shows this.

Example 15.1

USE tempdb;

SELECT name, create_date FROM tempdb.sys.tables

WHERE name LIKE ‘#%’

The result for my instance at present is

Name                       create date

#employee                  2019-05-03 11:44

#project                   2019-05-03 11:45

If you want to see how much space is being used for the tempdb database of your Database Engine instance, you can use the sys.dm_db_file_space_usage dynamic management view. Example 15.2 shows the use of this DMV.

Example 15.2

USE tempdb;

SELECT user_object_reserved_page_count AS user_pages,

internal_object_reserved_page_count AS internal_obj_pages,

version_store_reserved_page_count AS version_store_pages

FROM tempdb.sys.dm_db_file_space_usage;

Example 15.2 displays the information concerning three types of objects in the tempdb system database. The user_object_reserved_page_count column displays the number of pages allocated for user objects. The internal_object_reserved_page_count column displays the number of pages allocated for internal objects. The version_store_reserved_page_count column specifies the number of pages allocated for the version store.

4. msdb Database

The msdb database is used by the component called SQL Server Agent to schedule alerts and jobs. This system database contains task scheduling, exception handling, alert management, and system operator information; for example, the msdb database holds information for all the operators, such as e-mail addresses and pager numbers, and history information about all the backups and restore operations. For more information regarding how this system database can be restored, see Chapter 16.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *