Introduction

This article throws light on the System databases which primarily hold the User databases together. I feel the knowledge of how SQL Server manages User databases and knowing the contribution of each of the System databases is very essential for developers and DBAs alike.

System databases are used to store system-wide data and metadata.

There are six system databases:

• Master
• Model
• MSDB
• TempDB
• Resource
• Distribution

They may initiate questions and doubts, won't they? But I can see only the first four under my System Databases catalog on the SSMS Object Explorer. Truly so. I will explain soon what the Resource and Distribution databases are for.

The System databases are comprised of Master, Model, MSDB, TempDB, and the hidden Resource database. If the server is configured to be a replication distributor, there will also be at least one system distribution database that is named during the replication configuration process. Let us discuss each of them in detail.

Master

The Master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. Also, Master is the database that records the existence of all other databases and the location of those database files, and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the Master database is unavailable. Unlike its predecessors, SQL Server 2005 and later versions do not store system information in the Master database, but rather in the Resource database. However, system information is logically presented as the SYS schema in the Master database. Physically, the Master, Model, TempDB, and MSDB databases are present in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.

For the Master database, the data and log files are placed in the above path with the names master.mdf and mastlog.ldf.

There is a few restrictions on the Master database; the following operations cannot be performed on the Master database:

• Adding files or filegroups.
• Changing collation. The default collation is the server collation.
• Changing the database owner. Master is owned by dbo.
• Creating a full-text catalog or full-text index.
• Creating triggers on system tables in the database.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.

Do remember to:

While working with master database:

• Always have a current backup of the Master database available.
• Back up the Master database as soon as possible after the following operations:
• Creating, modifying, or dropping any database
• Changing server or database configuration values
• Modifying or adding logon accounts
• Do not create user objects in Master. Otherwise, Master must be backed up more frequently.
• Do not set the TRUSTWORTHY option to ON for the Master database.

Model

The Model database is a template database. Whenever a new database is created (including the system database TempDB), a copy of the Model database is created and renamed with the name of the database being created. The advantage of this behavior is that objects can be placed in the Model database prior to the creation of any new database and, when the database is created, the objects will appear in the new database.

For example, just in case you need to create a Stored Procedure or a function and need its availability across several databases, the best approach is to place it on the Model database. To show this, explicitly create a dbo.Msg function in the Model database, following which create two databases MyDB and DEMO. Now this function will be available from within the databases created after the function.

USE Model
GO
CREATE FUNCTION dbo.Msg (@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
SET @String='Running this from Model for '+@String+' Database' 
RETURN @String
END

CREATE DATABASE MyDB;
USE MyDB
GO
SELECT dbo.Msg('MyDB');
-----------------------------------------
Running this from Model for MyDB Database

(1 row(s) affected)

CREATE DATABASE DEMO;
USE DEMO
GO
SELECT dbo.Msg('DEMO');
-----------------------------------------
Running this from Model for DEMO Database

(1 row(s) affected)

After creating this function in the Model database, it will be propagated to all the databases created after adding it to the Model database and can be utilized with the simplified code. Conversely, the databases created prior to the creation of the function will not be able to access it. In this example, the database TestDB already existed prior to the creation of the function dbo.Msg on the Model database, hence the query below throws an error:

USE TestDB
GO
SELECT dbo.Msg('TestDB');
-----------------------------------
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function 
       or aggregate "dbo.Msg", or the name is ambiguous.

Any database object can be added to the Model database so that they are available in subsequently created databases. This includes database users, roles, tables, Stored Procedures, functions, and assemblies.

MSDB

MSDB database is the SQL Server Agent’s database. That’s because the SQL Server Agent uses the MSDB database extensively for the storage of automated job definitions, job schedules, operator definitions, and alert definitions. The Agent is responsible for almost all automated and scheduled operations. And MSDB contains all the jobs. For starting any job, getting the status or stopping job commands are to be run under the MSDB database.

Use msdb
GO
sp_help_job
sp_start_job
sp_stop_job
GO

The SQL Server Agent is not the only service that makes extensive use of the MSDB database. Service Broker, Database Mail, and Reporting Services also use the MSDB database for the storage of scheduling information. In addition to automation and scheduling information, SQL Server Integration Services (SSIS) can also utilize the MSDB database for the storage of SSIS packages.

TempDB

The TempDB database is used by SQL Server to store data—yes, it is temporary. The TempDB database is used extensively during SQL Server operations, so careful planning and evaluation of its size and placement are critical to ensure efficient SQL Server database operations. The TempDB database is used by the database engine to store temporary objects (such as temporary tables, views, cursors, and table-valued variables) that are explicitly created by database programmers.

In addition, the TempDB database is used by the SQL Server database engine to store work tables containing intermediate results of a query prior to a sort operation or other data manipulation. For example, if you wrote a query that returned 100 rows and you wanted the results sorted by a date value in the results, SQL Server could send the unsorted results to a temporary work table where it would perform the sorting operation and then return the sorted results to you. If online index operations are performed, the TempDB database will hold the index during the build or rebuild process.

Another important aspect to keep in mind about the TempDB database is that all database users have access to it and have the ability to create and populate temporary objects. This access can potentially create locking and size limitation issues on SQL Server, so it is important to monitor the TempDB database just like any other database on SQL Server.

Resource

The next system database is the Resource database. The Resource database is a read-only database that contains all the system objects used by an instance of SQL Server. The Resource database is not accessible during normal database operations. It is logically presented as the SYS schema in every database. It contains no user data or metadata. Instead, it contains the structure and description of all system objects.

This design enables the fast application of service packs by just replacing the existing Resource database with a new one. As an added bonus, to roll back a service pack installation, all you have to do is replace the new Resource database with the old one. This very elegant design replaces the older method of running many scripts that progressively dropped and added new system objects.

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. By default, these files are located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file. The Resource database depends on the location of the Master database. Remember, if you move the Master database, you must also move the Resource database to the same location.

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');
GO
----------
10.00.1600

To determine when the Resource database was last updated, use:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO
-----------------------
2008-07-09 16:50:29.033

Distribution

The last system database is Distribution. Once the sever instance is configured for replication to act as a distributor, the Distribution database gets created in the catalog. The Distribution database stores metadata and history data for all types of replication, and transactions for transactional replication.

For beginners in MS SQL Server database development, I feel it’s necessary to be well aware of the basics of system databases and what each one is used for. Hope this article adds a little value to them.