SQL Server System Databases

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. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. 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.

Physical Properties of master:

The following table lists the initial configuration values of the master data and log files. The sizes of these files may vary slightly for different editions of SQL Server.

File                         Logical name         Physical name       File growth

Primary data        master                     master.mdf            Autogrow by 10 percent until the disk is full.

Log                         mastlog                   mastlog.ldf             Autogrow by 10 percent to a maximum of 2 terabytes.

Database Options:

To view the current settings for these options, use the sys.databases catalog view.

Recommendations:

When you work with the master database, consider the following recommendations:

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. If you do, master must be backed up more frequently.

Do not set the TRUSTWORTHY option to ON for the master database.

What to Do If master Becomes Unusable:

If master becomes unusable, you can return the database to a usable state in either of the following ways:

Restore master from a current database backup.

If you can start the server instance, you should be able to restore master from a full database backup.

Rebuild master completely.

If severe damage to master prevents you from starting SQL Server, you must rebuild master.

Restrictions

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 sa.

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.

Model:

The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system.

Newly created user databases use the same recovery model as the model database. The default is user configurable.

Model Usage

When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.

If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures. File properties of the model database are an exception, and are ignored except the initial size of the data file. The default initial size of the model database data and log file is

8 MB.

Database Options:

To view the current settings for these options, use the sys.databases catalog view.

Restrictions

The following operations cannot be performed on the model database:

Adding files or filegroups.

Changing collation. The default collation is the server collation.

Changing the database owner. model is owned by sa.

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 primary filegroup to READ_ONLY.

Creating procedures, views, or triggers using the WITH ENCRYPTION option. The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.

MSDB:

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb. This information includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored. SQL Server Management Studio uses this information to propose a plan for restoring a database and applying any transaction log backups.

Backup events for all databases are recorded even if they were created with custom applications or third-party tools. For example, if you use a Microsoft Visual Basic application that calls SQL Server Management Objects (SMO) objects to perform backup operations, the event is logged in the msdb system tables, the Microsoft Windows application log, and the SQL Server error log. To help your protect the information that is stored in msdb, we recommend that you consider placing the msdb transaction log on fault tolerant storage.

By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb.

Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.

Physical Properties of msdb:

The following table lists the initial configuration values of the msdb data and log files. The sizes of these files may vary slightly for different editions of SQL Server Database Engine.

File                          Logical name               Physical name                            File growth

Primary data         MSDBData                   MSDBData.mdf                          Autogrow by 256 KB until the disk is full.

Log                          MSDBLog                     MSDBLog.ldf                               Autogrow by 256 KB to a maximum of 2 terabytes.

Database Options:

To view the current settings for these options, use the sys.databases catalog view.

Restrictions

The following operations cannot be performed on the msdb database:

Changing collation. The default collation is the server collation.

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 primary filegroup to READ_ONLY.

Tempdb:

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Performance Improvements in tempdb:

In SQL Server, tempdb performance is improved in the following ways:

Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.

Allocation page latching protocol is improved. This reduces the number of UP (update) latches that are used.

Logging overhead for tempdb is reduced. This reduces disk I/O bandwidth consumption on the tempdb log file.

Setup adds multiple tempdb data files during a new instance installation. This task can be accomplished with the new UI input control on the Database Engine Configuration section and a command line parameter /SQLTEMPDBFILECOUNT. By default, setup will add as many tempdb files as the CPU count or 8, whichever is lower.

When there are multiple tempdb data files, all files will autogrow at same time and by the same amount depending on growth settings. Trace flag 1117 is no longer required.

All allocations in tempdb use uniform extents. Trace flag 1118 is no longer required.

For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

Database Options:

To view the current settings for these options, use the sys.databases catalog view.

Restrictions

The following operations cannot be performed on the tempdb database:

Adding filegroups.

Backing up or restoring the database.

Changing collation. The default collation is the server collation.

Changing the database owner. tempdb is owned by sa.

Creating a database snapshot.

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.

Running DBCC CHECKALLOC.

Running DBCC CHECKCATALOG.

Setting the database to OFFLINE.

Setting the database or primary filegroup to READ_ONLY.

Permissions:

Any user can create temporary objects in tempdb. Users can only access their own objects, unless they receive additional permissions. It is possible to revoke the connect permission to tempdb to prevent a user from using tempdb, but this is not recommended as some routine operations require the use of tempdb.

Resource database:

The mssqlsyetemresource data base is hidden database and it is usually referred as resource data base.

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

Physical Properties of Resource:

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL.<instance_name>\MSSQL\Binn\ and should not be moved. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

Backing Up and Restoring the Resource Database:

SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

Accessing the Resource Database:

The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.

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

SELECT SERVERPROPERTY('ResourceVersion'); 

GO 

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

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime'); 

GO 

To access SQL definitions of system objects, use the OBJECT_DEFINITION function:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects')); 

GO