When you installed SQL Server several system databases are created automatically and these databases are master, model, msdb, tempdb and resource. These are used by the system and should not be removed.

Master Database

The master database contains all the system level information and all databases created. The information in this database includes file allocation, disk space usage, system wide settings, user accounts, and the existence of other system databases. Therefore, SQL Server cannot start if the master database is unavailable.

Model Database

The model database provides a template for all newly created databases. Basically SQL Server takes a copy of Model database whenever user tries to create a new database in SQL Server. It means that if a user creates any tables, stored procedures, user defined data types or user defined functions within a Model database, then those objects will be available in every newly created database on that particular instance of SQL Server.

Msdb Database

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail. It also stores information related to configuration of Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail, Policy Based Information of SQL Server 2008 etc.

Tempdb Database

This database is a workspace for holding temporary objects or intermediate result sets, temporary table creation. In SQL Server, tempdb database is recreated every time SQL Server Service is restarted. This also means that the some of the settings of Model database are also used when tempdb is created.

Resource Database

The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which is physically available only in the Resource database, they logically appear in the SYS schema of every database. The Resource Database does not contain any user data.

  Modified On Sep-18-2014 01:23:58 PM

Leave Comment