Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server System Defined Database

  Author : Shailendra Chauhan
Updated On : 02 Oct 2011
Total Views : 120,821   
 

SQL Server 2008 and 2005 have five system defined databases: master, model, tempdb, msdb, and resource. These databases are used by SQL Server for its own operation and management. Let’s see these database use and function.

Master Database

  1. Master database contains all of the System level information for 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 databases 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 accounts 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 users details for Sql Server. We can see the user information by using the system defined "SYSUSERS" table

     Select * from SYSUSERS 

  6. Primary data of Master database is stored in master.mdf file which default size is 11 MB and Master database log is stored in Masterlog.ldf file which default size is 1.25 MB.

TempDB Database

  1. TempDB databse 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 school database.

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

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

  2. TempDB database is recreated every time when we re-start the SQL Server. Hence, when the database server gets restarted, the temporary objects will be removed from TempDB database.

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

Model Database

  1. Model database work as a template for all the databases created on Sql Server.

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

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

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

MSDB Database

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

    Example If we create a Database Maintenance Plan to take backup of a particular database on daily basis, then each and every entry will be stored in system defined “SYSJOBS” table in MSDB Database.

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

Resource Database

  1. Resource database is also a system defined database that is hidden from user view like as 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')) 

Summary

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

YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
11 DEC
ASP.NET MVC with AngularJS Development (offline)

Sat-Sun 09:30 AM-11:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri     6:30 AM-7:30 AM IST

3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (11:00 AM-12:30 PM IST)

26 NOV
ASP.NET MVC with AngularJS Development (offline)

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

12 NOV
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

3 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+