System database in sql server

System Database

     This database is used for various activities that are going to be performed within SQL Server.

 

(1)  Master: - This database record all the system level into or operations that are going to be performed with in SQL Server (Root Database of the SQL Server).

 

(2)  Model: - It acts as a template for every new database that is created in SQL Server when we crate a database SQL Server will copy the complete structure from the model and using that structure it will create the

                                                                

            

(3)  Temp database: - This database holds the temporary objects that are created in SQL Server because temporary objects requires to be destroyed whenever SQL Server is shutdown, So without going and verifying in to each database to identify the temporary objects, It uses these mechanism. Whenever SQL Server is shutdown the complete tempDB is dropped so all the temporary tables present in it are destroyed and whenever SQL Server restarts it will recreated a tempDB database again by copying the structure from model database.

                                                                       

 

 

(4)  MSDB: - It is a database, which is used for storing information of jobs, alerts and schedules.                

 

         JOB: - It is an activity that has to be performed a schedule specifies when the action or  activity has to be performed.     

         ALERT: - It is messages that pop up to the use on some prescribed situations.

 

Note :  Every Database System or User is internally collection of two files

(1)  .mdf: - Stands for Master Data File this contains the information of the actual tables that are present in the data along with their data.

 

(2)  .ldf: - Stands for log data file which holds the information of the DML activities that are performed on the database.

Note

        The .mdf, .ldf files sits in the data folder of the SQL Server installation folder.