Voluntary DBA

We know they’re important — they sit in that special folder and all — but what do they really do? Why are they special? Let’s take a closer look.
 

master

This database is the gatekeeper of SQL Server.

It contains all the system configuration settings and logins, in addition to other instance-wide objects such as linked servers.

Most importantly, though, it contains all the information needed to find all the other databases in the instance. The SQL Server service is started by default with several command-line parameters, but two of those are the file locations of the master database data file and log file. When SQL Server starts up, it reads the master database to find out where all the databases in the instance are, including all the system databases, and then goes through the recovery process on them.

On a production system, taking regular backups of master is essential.
 

model

As the name suggests, this database is the template from which all new databases are created.

What’s in this database? By default it’s empty, and you can put pretty much anything you want in there — new databases will have those objects in it by default. (Note: this isn’t necessarily a best-practice; I’m just illustrating how it works.) Typically, this database is left empty, and database settings are adjusted to suit the environment. The recovery model is the most common setting changed.

It’s recommended to take backups of this database, even though it’s relatively easy to replace.
 

msdb

This database stores everything having to do with SQL Agent, including all the jobs and their history, schedules, and alerts. It’s also used as a staging area for other features such as Database Mail.

The biggest thing to be aware of with msdb is the size of the job history. Depending on what you have running on your system, this can grow extremely quickly, and can in some cases be very difficult to clean up. Watch the size of this database! A default installation of SQL Server lands this database’s files on the C drive, which is not a drive you want to fill up unexpectedly.

In terms of criticality, this is the second-most important system database behind master, and regular backups are essential on a production system. As mentioned, it’s also good to monitor the size of this database, and keep tabs on what kind of job history is being tracked.

tempdb

This system database is special in that unlike any of the others, it’s recreated from scratch every time the instance starts. Instant data file initialization can improve SQL Server restart time immensely because tempdb gets recreated on start up. Less restart time means higher availability.

Not only is tempdb temporary itself, it’s also responsible for holding temporary objects that get created by user scripts and internal operations. This includes things like temporary tables and table variables, row version information for snapshot isolation, and extra space as needed to satisfy user queries that do big sorts or joins.

While you can’t take backups of tempdb, it’s important for it to be well-tuned to your workload. Tuning tempdb is a topic unto itself, so I won’t discuss that further here.

distribution

This database is used by SQL Server Replication to store information about transactions that have occurred which need to be sent out to subscribers. If Replication is not configured, this database won’t exist in the list. Note also that the name of this database can vary, as it’s user-entered. You can run the following query to find distribution databases in your instance:

SELECT
	name
	FROM sys.databases
	WHERE is_distributor = 1

Resource (mssqlsystemresource)

This is SQL Server’s internal system database, and is hidden from direct viewing. It contains internal objects such as system catalog views and stored procedures that appear in the sys schema in every database.

This database can’t be backed up through SQL Server, and should not be modified.

Although this post certainly isn’t a comprehensive list, hopefully I’ve given you a basic understanding of what’s in the system databases. If you want to learn more, you can read the TechNet articles that go into more detail.