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.
