System Databases

3 4

When you install SQL Server, four system databases are created: the master, tempdb, model, and msdb databases. These databases are described in the following list:

• master Records the system level information, SQL Server initialization information, and configuration settings for SQL Server. This database also records all login accounts, the existence of all other databases, and the location of the primary file for all user databases. Always keep a recent backup of the master database.
• tempdb Holds temporary tables and temporary stored procedures. This database is also used for other temporary storage needs of SQL Server, such as for sorting data. A clean copy of the tempdb database is re-created at its default size every time SQL Server is started. It then grows automatically, as necessary. If you need a large amount of tempdb space, you can increase the database's default size by using the ALTER DATABASE command.
• model Serves as a template for all other databases created on the system, including tempdb. When a database is created, the first part of it is created as a copy of the contents of the model database. The rest of the database is filled with empty pages. The model database must exist on the system because it is used to re-create tempdb every time SQL Server is started. You can alter the model database to include user-defined data types, tables, and so on. If you alter the model database, each database you create will have the modified attributes.
• msdb Holds tables that SQL Server Agent uses for scheduling jobs and alerts and for recording operators. (Operators are individuals who are assigned responsibility for jobs and alerts.) This database also holds tables used for replication.

Each of these system databases has its own primary data file and log file. The databases are stored in the folder you specified for system files during SQL Server installation.