Every SQL Server relies on four primary system databases, each of which must be present for the server to operate effectively. New database administrators and those who have never set up a server from scratch may find these databases mysterious or intimidating, but it’s important to understand their purposes and some of the basic maintenance that should be performed on them to keep your system running smoothly. Let’s go over the big four system databases.
master
The master database stores basic configuration information for the server. This includes information about the file locations of the user databases, as well as logon accounts, server configuration settings, and a number of other items such as linked servers and startup stored procedures. While it is possible to rebuild the master database, a better practice is to take regular backups and to restore the database if a problem arises. Restoring the master database involves a few more steps than a user database, so it’s probably a good idea to practice this on a non-production machine before you need to try it in a real downtime situation.
model
The model database is a template database that is copied into a new database whenever it is created on the instance. Database options set in model will be applied to new databases created on the instance, and any objects created in model will be copied over as well. Even on a system where new databases are created infrequently, the model database must exist because it is used to create tempdb every time the server starts. It’s a best practice to backup model whenever a change is made.
msdb
The msdb database is used to support a number of technologies within SQL Server, including the SQL Server Agent, SQL Server Management Studio, Database Mail, and Service Broker. A great deal of history and metadata information is available in msdb, including the backup and restore history for the databases on the server as well as the history for SQL agent jobs. This database can grow out of control in some circumstances, so it’s important to make sure that you are only keeping as much history as you expect to make use of. Like the other databases mentioned so far, regular msdb backups are recommended. While the database ships in the simple recovery model, it is a best practice to change to the full recovery model and take log backups if you are making use of the backup and restore history tables.
tempdb
The tempdb system databases is a shared temporary storage resource used by a number of features of SQL Server, and made available to all users. Tempdb is used for temporary objects, worktables, online index operations, cursors, table variables, and the snapshot isolation version store, among other things. It is recreated every time that the server is restarted, which means that no objects in tempdb are permanently stored. Tempdb configuration is a large and important topic, as misconfiguration can have a major impact on the performance of an entire server. You can review the best practices for tempdb in this whitepaper. Since tempdb is non-permanent storage, backups and restores are not allowed for this database. You can also get a nice view of your tempdb database and get an idea of whether it is experiencing performance issues using SQL Diagnostic Manager‘s tempdb monitoring feature, which you can access during a free trial.
So there you have it: master, model, msdb, and tempdb. For further discussion on this topic, take a look at my webcast on Care and Feeding of Your System Databases over at MSSQLTips.