SQL Server 2005 System Databases - 20 Nov 2006

Although SQL Server's main purpose is to store user database information to support applications, SQL Server 2005 itself uses six system databases to help it perform this function. Let's look at how these databases help SQL Server 2005 do its job.

6: tempdb

The tempdb database contains temporary tables and stored procedures that client applications use. It's also used for SQL Server's own working tables. The tempdb database is deleted and recreated each time SQL Server starts, so objects in tempdb persist only until the next system restart. The tempdb database is implemented in the tempdev.mdf and templog.ldf files.

5: distribution

The distribution database is present only if you've configured replication and specified that the server act as a distributor. The distribution database stores all of the data sent from the replication publisher to the subscribers. The distribution.mdf and distribution_log.ldf files contain the distribution database.

4: Resource

Although by default this database doesn't appear in SQL Server Management Studio (SSMS), the Resource database is a system database that's used by SQL Server 2005. Unlike the other system databases, the Resource database is read-only. It contains copies of all of the system objects for SQL Server 2005.The Resource database uses the mssqlsystemresource.mdf and mssqlsystemresource.ldf files.

3: model

SQL Server 2005's model database is used as a template for all databases created on the SQL Server system. When a new database is created, (including the tempdb database, which is re-created each time SQL Server starts), the contents of the model database are copied to it. Changes you make to the model database will show in subsequent newly created databases. The modeldev.mdf and modellog.ldf physical files implement the model database.

2: msdb

This database keeps track of all SQL Server Agent jobs and alerts. It also implements log shipping in SQL Server 2005. The msdb database uses the msdbdata.mdf and msdblog.ldf files.

1: master

Contained in the master.mdf and mastlog.ldf physical files, the master database stores SQL Server system information. It records system configuration settings, system and user databases, and login information. Here's where you'll find all of the system-supplied stored procedures as well.