jayeshs
Whar are the various system databases used in SQL Server 2005
Posted by jayeshs in SQL Server on Jul 26, 2006
  • 0
  • 1
  • 5645
Do you know the answer for this question? Post it below.
Guest
Posted by jayeshs on Jul 26, 2006
  • 0

master Database - Records all the system-level information for an instance of SQL Server.
It records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable. In SQL Server 2005, system objects are no longer stored in the master database; instead, they are stored in the Resource database.



model Database - Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.



msdb Database - Is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.
 
Resource Database - Is a read-only database that contains system objects that are included with SQL Server 2005. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
 
tempdb Database - Is a workspace for holding temporary objects or intermediate result sets.
tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Backup and restore operations are not allowed on tempdb.


Most Popular Companies

Most Popular Job Functions

MOST LIKED QUESTIONS