Browse Tutorials
SQL Server System Defined Database

SQL Server System Defined Database

24 Mar 2024
Intermediate
138K Views
6 min read

SQL Server System Defined Database: An Overview

SQL Server 2008 and 2005 contain five system-defined databases: master, model, tempdb, msdb, and resource. SQL Server relies heavily on these databases to function and administer itself. Understanding their responsibilities and functionalities is essential for anyone starting a SQL Server Tutorial or taking a SQL Server Certification Course. Let's look at the specific purposes and functions of each of these databases.

Master Database

  1. The master database contains all of the System-level information for the SQL Server.

  2. It contains all system configuration settings details for SQL Server. We can see the system configuration information by using the system-defined "SYSCONFIGURES" table.

    Select * from SYSCONFIGURES 
  3. It contains all existing database details for SQL Server. We can see the database information and where the actual file persists by using the system-defined "SYSDATABASES" table

    Select * from SYSDATABASES 
  4. It contains all login account details for SQL Server. We can see the login account information by using the system-defined "SYSXLOGINS" table.

    Select * from SYSXLOGINS 
  5. It contains all user details for the SQL Server. We can see the user information by using the system-defined "SYSUSERS" table

    Select * from SYSUSERS 
  6. Primary data of the Master database is stored in the master.mdf file whose default size is 11 MB and the Master database log is stored in Masterlog.ldf file whose default size is 1.25 MB.

TempDB Database

  1. TempDB database contains all temporary objects like temporary tables and temporary stored procedures for SQL Server. The temporary objects are created by preceding “#” to the temporary object name.

    Example

    #tEmp table gets created in the school database.

    Use School
    CREATE TABLE #tEmp
    (
    EmpID int
    EmpName Varchar(50)
    ) 

    When the above TSQL statement gets executed, if we look at the tempDB, this temporary table will exist there. Note that we create this table in the School Database. But it exists in tempDB. We can access the #tEmp table from any other database.

  2. TempDB database is recreated whenever we restart the SQL Server. Hence, when the database server gets restarted, the temporary objects will be removed from the TempDB database.

  3. Primary data of the TempDB database is stored in tempDB.mdf file whose default size is 8 MB and the TempDB database log is stored in templog.ldf file whose default size is 0.5MB.

Model Database

  1. The model database works as a template for all the databases created on Sql Server.

  2. If we want to keep some generic database objects like tables, and function-stored procedures in the newly created database then we put these objects into the Model database. Hence when we create a new database then available data objects in the Model database, would be copied into the newly created database.

    Note: At the creation of a new database, if any database connection is opened for the Model database then We can not able to create a new database.

  3. Primary data of the Model database is stored in the model.mdf file whose default size is 0.75 MB and the Model database log is stored in modellog.ldf whose default size is 0.75MB.

MSDB Database

  1. MSDB database is used by SQL Server Agent to schedule alerts, and jobs, and to record operators.

    For example, If we create a Database Maintenance Plan to take a backup of a particular database daily, then every entry will be stored in a system-defined “SYSJOBS” table in the MSDB Database.

  2. Primary data of the Msdb database is stored in msdbdata.mdf file whose default size is 12 MB and the Msdb database log is stored in msdblog.ldf whose default size is 2.25MB.

Resource Database

  1. A resource database is also a system-defined database that is hidden from user view like another system-defined database. It contains the system-defined objects.

  2. We can see the Resource database file by navigating to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn.

  3. Using the OBJECT_DEFINITION system function, we can view the contents of the resource database.

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

Read More

Summary

In this article, I try to explain the System-defined database in SQL Server. I hope after reading this article you will be aware of a system-defined database in SQL Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

FAQs

Q1. What is a system-defined database in SQL?

System databases are defined by Microsoft and required for SQL Server to function. These databases include the Master, Model, MSDB, TempDB, Resource, and Distribution databases, which are utilized in replication, as well as the ReportServer and ReportServerTempDB databases for reporting services.

Q2. What is the purpose of SQL Server's master database?

The master database contains system-wide information, such as server setup settings, login information, and metadata for all other databases on the server.

Q3. Why is the SQL Server model database so important?

The model database acts as a template for establishing new databases, containing default settings and objects that are copied to new databases as they are formed.

Q4. What is the msdb database's role in SQL Server?

The msdb database contains data on SQL Server Agent jobs, backup and restore history, maintenance plans, and other system-related operations.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this