System Database in sql server 2012

System database is a database which is already exists in database when we install the sql server. It is used for Monitoring, Management and maintenance purpose.

Below is the system database in sql server 2012

Ø  Master

Ø  Model

Ø  Msdb

Ø  TempDB

Ø  Resource (mssqlsystemresource)

Ø  Distribution

Ø  Report server

Ø  Report server TempDB

Master Database

This is the core system database to manage the sql server instance. Master database is the logical repository for the system object receding in the system schema. It contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings and also contains information about the other databases on this instance and the location of their files.

If the master database is not present, SQL Server cannot start. 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.

Some example of Master database tables

--Get the configuration details select * from sysconfigures

Get the information of the configuration.

--Get the existing database select * from sysdatabases

--Get the details of user select * from sysusers

TempDB Database

The TempDB database is used to store temporary tables generated automatically by SQL Server and the temporary tables created explicitly by the users. Temp table start with # (local temp table) or ## (Global temp table). We can say the all temporary objects are store in TempDB. Object may be table, cursor, sp etc.

When we close the instance of SQL Server it is automatically destroy.

See the example

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. 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.

MSDB Database

This is the primary database for manage the sql server agent configuration. It is used by SQL Server Agent to schedule alerts, jobs, and to record operators. 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.

Some System table in MSDB Database.

Model Database

Model database is store the template for the all user defined database. User define object may be table, view, functions or Stored procedures. All can be can be created in the model database and will exist in all future user define database. 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 database objects in Model database, would be copied into newly created database.

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.

Resource Database

Resource database is also a system defined database that is hidden from user view. This is responsible for physically store all of the sql server system objects. This Database has been created to improve the upgrade and rollback of the sql server with ability to over view only the database. Using the Object_Definition system functions we can see contain of the resource databases.

-- Return visible system objects in master database context select OBJECT_DEFINITION(OBJECT_ID('sysobjects'))

   

This is read-only database that is not accessible via the sql server tool set. And it doesn’t have entry in the Master.sys.database  

Distribution

Primary data to support the sql server replication. It is responsible for the replication metadata and supports the data for transaction replication between the publisher and subscribers.

Report Server

Primary database for reporting services to store the metadata and object definition

Ø  Report security

Ø  Job schedulers and running jobs

Ø  Report notifications

Ø  Report execution history

Report Server TempDB

Temporary storage for reporting services like session information and cache information.

We can’t drop the system database.