SQL Server System Defined Database

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'))