Types of Database

Types of Databases in SQL Server

  1. System Databases
  2. User Databases

Following are SQL Server System Databases:

system database
System DatabaseDescription
masterRecords all the system-level information for an instance of SQL Server.
msdbIs used by SQL Server Agent for scheduling alerts and jobs.
modelIs used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
ResourceIs a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
tempdbIs a workspace for holding temporary objects or intermediate result sets.
System Databases

System Database Detailing

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.

FileLogical namePhysical nameFile growth
Primary datamastermaster.mdfAutogrow by 10 percent until the disk is full.
Logmastlogmastlog.ldfAutogrow by 10 percent to a maximum of 2 terabytes.
master – system database
Restrictions

The following operations cannot be performed on the master database:

  • Adding files or filegroups.
  • Backups, only a full database backup can be performed on the master database.
  • 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.
Recommendations

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

  1. Always have a current backup of the master database available.
  2. 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.
  3. Do not set the TRUSTWORTHY option to ON for the master database.

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. 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. For more information, see Recovery Models (SQL Server). 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.

FileLogical namePhysical nameFile growth
Primary dataMSDBDataMSDBData.mdfAutogrow by 10 percent until the disk is full.
LogMSDBLogMSDBLog.ldfAutogrow by 10 percent to a maximum of 2 terabytes.
msdb – system database
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.

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.

FileLogical namePhysical nameFile growth
Primary datamodeldevmodel.mdfAutogrow by 64 MB until the disk is full
Logmodellogmodellog.ldfAutogrow by 64 MB to a maximum of 2 terabytes
model – system database
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.

Resource Database

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 :\Program Files\Microsoft SQL Server\MSSQL.\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.

tempdb

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.

tempdb never has anything to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

The tempdb system database is a global resource that’s available to all users connected to the instance of SQL Server or connected to Azure SQL Database. tempdb holds:

  1. Temporary user objects that are explicitly created. They include global or local temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors.
  2. Internal objects that the database engine creates. They include:
  • Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
  • Work files for hash join or hash aggregate operations.
  • Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
FileLogical namePhysical nameInitial sizeFile growth
Primary datatempdevtempdb.mdf8 MBAutogrow by 64 MB until the disk is full
Secondary data filestemp#tempdb_mssql_#.ndf8 MBAutogrow by 64 MB until the disk is full
Logtemplogtemplog.ldf8 MBAutogrow by 64 MB to a maximum of 2 terabytes
tempdb – system database
Permissions

Any user can create temporary objects in tempdb. Users can access only their own objects, unless they receive additional permissions. It’s possible to revoke the connect permission to tempdb to prevent a user from using tempdb. We don’t recommend it because some routine operations require the use of tempdb.

Restrictions

The following operations can’t 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.

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply