Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

II.1.1. System Databases

SQL Server ships with four built-in databases. Also known as system databases, these information repositories each play a significant role in keeping your data organized and tidy. Here's a brief look at them:

• Master: As you might guess from the name, this database means business: It keeps track of everything about your other databases, including where to find them, how they're configured, and even how to start up the database server. It also knows your security and other login settings.

• Model: SQL Server uses this database as a guideline for any new database that's created on your system.

• Msdb: Here's where the SQL Server Agent keeps track of its workload, such as scheduled jobs, alerts, Service Broker tasks, and database mail.

• Tempdb: This database fills the important job of serving as a temporary repository for transient information from both user tasks as well as internal SQL Server work. As you might imagine, it fills with all sorts of stuff over time. Luckily, every time you restart the database engine, SQL Server re-creates a fresh, empty copy of this database.

Figure 1-1 shows how system databases appear in the SQL Server Management Studio.

Figure II.1-1. System databases in the SQL Server Management Studio.