Microsoft SQL Server System Database

Microsoft SQL Server System Database

When we install SQL Server and open Management Studio , we notice that there are four  database comes automatically , master ,model, msdb , tempdb in Database /System Database itself.

How to get list of database from SQL Server?
select * from sys.databases
Result 
master
tempdb
model
msdb
sudent

A) master :The database hold all system level information . Also record all database present in the instance .For example, if we ass add "Student" database , master database will record the information .It also contain initialization information od SQL server.If the database is corrupted , Sql server create problem , There are several method to retrieve the master database. The physical file name of master database is master.mdf  . Newer version of SQL Server system level information have been moved to resource database.

B)model :It is a template of all database .For example , if we created "student" database .The structure will be copied from model . Another good example is , when we start SQl Server  tempdb is created .It also copied from the template of model . Sql Server make sure that model database must contain, before instance of sql server created . As an example , when we run "Create Database"  command ,

first entire structure of model database tables , function , stored procedures will be copied to the new dababase from model then rest work progress .The initial size of model database is 8Mb , physical file model.mdf .Database owner "sa", you cannot change it .you cannot drop or rename it.

C)msdb : msdb is responsible for all joining and maintenance plan and DTS pakage.It is also responsible for backup history .Database mail , Sql Server agent also maintain by msdb .Jobs ,log shipping is also msdb responsibility .It also hold information of which database backup was last taken .Physical file name of MSDGData.mdf , also responsible for simple recovery model.

D) tempdb : tempdb has an huge work. When we create a temptable , it actually create on tempdb.

When we use table variable , function return table, it also create on tempdb. It is also , responsible for pasring query , cursor, trigger and also user define function .Internal object created by Sql Server also hold by tempdb.Sql server transaction management is done with the help of tempdb. Commit, rollback.

How to see temp table created by code in sql server?

create table #temp

(

id int

)

SELECT  * FROM tempdb.dbo.sysobjects O

WHERE O.xtype in ('U')

AND O.ID = OBJECT_ID(N'tempdb..#temp')

Name       id

#temp_..         -1503307154