SQL Server System Defined Database

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

• Top SQL Server Interview Questions and Answers

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.

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