SQL Server System Databases

Master:Records all the system-level information for an instance of SQL Server.
Msdb: Is used by SQL Server Agent for scheduling alerts and jobs.
Model: 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.
Resource: Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
Tempdb: Is a workspace for holding temporary objects or intermediate result sets.

Every instance of SQL Server has a single TempDB system database which is shared by all other databases on the same instance. Since SQL Server 2005, TempDB has been used more and more with every new SQL Version.

TempDB is used for the following purpose:

1. User Objects:
   1. Local and Global Temporary Tables and their indexes
   2. Table Variables
   3. Temp Procedures
2. Internal Objects:
   1. Work tables for Hash operations and aggregates
   2. Work tables for DBCC CHECKDB and DBCC CHECKTABLE
   3. Work files needed for SORT, GROUP BY, ORDER BY, UNION operations
   4. CTEs: Common Table Expressions
   5. Storing Temporary LOB storage: If they won’t fit in Memory
   6. Work tables for processing Service Broker objects
3. Version Store:
   1. Online Index Operations
   2. MARS- (Multiple Active Result Sets)
   3. Snapshot Isolation
   4. Read Committed Snapshot Isolation

Few important restrictions we have on TempDB:

1. Cannot remove PRIMARY data file or log file
2. Cannot rename or drop TempDB database
3. Cannot make the TempDB OFFLINE
4. Cannot add, remove or rename FILEGROUP
5. Cannot change the Collation , default is Server Collation
6. Cannot change the owner of the TempDB, its owner is always dbo.
7. Cannot BACKUP or RESTORE TempDB