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.
End of Article
