As a Database Administrator, it’s crucial to know about the system database in SQL Server. In this post we will cover the basics of all system databases.
Master Database:
Master database holds all server configuration information. This includes Instance configuration, Login details, linked server information and all database details (including the file location). From SQL 2005, all system objects are stored logically in master database (System objects are physically available in resource database). If master database is not-available/unusable/corrupt SQL instance will not start. In that case we have to go for restore of master database or rebuild.
Model Database:
Model database is a template database. All setting made to model database will be applied to new databases that gets created. The entire content of model database is copied to the new database. Say if there is a requirement to have a specific object (table, stored procedure, functions) to be created by default whenever new database is created. To achieve this, just create the required table in model database.
Msdb Database:
Msdb database is used by SQL Server Agent for scheduling jobs/alerts, Database mail and Service broker. All the agent job related information is stored in msdb database. It’s highly important to make sure only required set of historical data is placed or else database growth will be uncontrollable.
Tempdb Database:
As name suggests it’s a temporary database, nothing inside this database is stored permanently. Tempdb gets recreated every time when SQL Instance is restarted. This database is a shared storage. Tempdb is used to store all temporary objects, online index rebuild operation, create/rebuild index sort in tempdb, sort operation, cursor and snapshot isolation. Backups and restore is not allowed for tempdb.
Resource Database:
Resource database is a read-only database introduced in SQL 2005. All the system objects are physically placed in this database. This makes upgrade/roll-back easier, updates just need to be applied to the resource database instead of having to go and modify each objects individually. Prior to SQL 2005 system objects was stored in master database. Resource database is not visible in management studio and is not listed in sys.databases as well. The data (mssqlsystemresource.mdf) and log file (mssqlsystemresource.ldf) of resource database can be located in “<drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn\”.
Please note there are few more system database which gets created while setting up Reporting services and configuring replication. We will discuss about the below two in a different post.
Distribution Database (Used for functioning replication)
ReportServer and ReportServerTempDB Database (Used for Reporting Service)
For more information on system database click here.
Cheers,
Naveen
Master Database:
Master database holds all server configuration information. This includes Instance configuration, Login details, linked server information and all database details (including the file location). From SQL 2005, all system objects are stored logically in master database (System objects are physically available in resource database). If master database is not-available/unusable/corrupt SQL instance will not start. In that case we have to go for restore of master database or rebuild.
Model Database:
Model database is a template database. All setting made to model database will be applied to new databases that gets created. The entire content of model database is copied to the new database. Say if there is a requirement to have a specific object (table, stored procedure, functions) to be created by default whenever new database is created. To achieve this, just create the required table in model database.
Msdb Database:
Msdb database is used by SQL Server Agent for scheduling jobs/alerts, Database mail and Service broker. All the agent job related information is stored in msdb database. It’s highly important to make sure only required set of historical data is placed or else database growth will be uncontrollable.
Tempdb Database:
As name suggests it’s a temporary database, nothing inside this database is stored permanently. Tempdb gets recreated every time when SQL Instance is restarted. This database is a shared storage. Tempdb is used to store all temporary objects, online index rebuild operation, create/rebuild index sort in tempdb, sort operation, cursor and snapshot isolation. Backups and restore is not allowed for tempdb.
Resource Database:
Resource database is a read-only database introduced in SQL 2005. All the system objects are physically placed in this database. This makes upgrade/roll-back easier, updates just need to be applied to the resource database instead of having to go and modify each objects individually. Prior to SQL 2005 system objects was stored in master database. Resource database is not visible in management studio and is not listed in sys.databases as well. The data (mssqlsystemresource.mdf) and log file (mssqlsystemresource.ldf) of resource database can be located in “<drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn\”.
Please note there are few more system database which gets created while setting up Reporting services and configuring replication. We will discuss about the below two in a different post.
Distribution Database (Used for functioning replication)
ReportServer and ReportServerTempDB Database (Used for Reporting Service)
For more information on system database click here.
Cheers,
Naveen
No comments:
Post a Comment
Please share your thoughts