System Databases

A new SQL Server 2005 installation always includes four databases: master, model, tempdb, and msdb. It also contains a fifth, "hidden" database that you will never see using any of the normal SQL commands that list all your databases. This database is referred to as the resource database, but its actual name is mssqlsystemresource.

master

The master database is composed of system tables that keep track of the server installation as a whole and all other databases that are subsequently created. Although every database has a set of system catalogs that maintain information about objects it contains, the master database has system catalogs that keep information about disk space, file allocations and usage, systemwide configuration settings, endpoints, login accounts, databases on the current instance, and the existence of other SQL servers (for distributed operations).

The master database is critical to your system, so always keep a current backup copy of it. Operations such as creating another database, changing configuration values, and modifying login accounts all make modifications to master, so after performing such actions, you should back up master.

model

The model database is simply a template database. Every time you create a new database, SQL Server makes a copy of model to form the basis of the new database. If you'd like every new database to start out with certain objects or permissions, you can put them in model, and all new databases will inherit them. You can also change most properties of the model database by using the ALTER DATABASE command, and those property values will then be used by any new database you create.

tempdb

The tempdb database is used as a workspace. It is unique among SQL Server databases because it's re-creatednot recoveredevery time SQL Server is restarted. It's used for temporary tables explicitly created by users, for worktables that will hold intermediate results created internally by SQL Server during query processing and sorting, for maintaining row versions used in snapshot isolation and certain other operations, and for materializing static cursors and the keys of keyset cursors. Because the tempdb database is re-created, any objects or permissions that you create in the database will be lost the next time you restart your SQL Server instance. An alternative is to create the object in the model database, from which tempdb is copied.

The tempdb database sizing and configuration is critical for optimal functioning and performance of SQL Server, so I'll discuss tempdb in more detail in its own section later in this chapter.

mssqlsystemresource

As mentioned, the mssqlsystemresource database is a hidden database and is usually referred to as the resource database. Executable system objects, such as system stored procedures and functions, are stored here. Microsoft created it to allow very fast and safe upgrades. If no one can get to this database, no one can change it, and you can upgrade to a new service pack that introduces new system objects by simply replacing the resource database with a new one. Keep in mind that you can't see this database using any of the normal means for viewing databases, such as selecting from sys.databases or executing sp_helpdb. It also won't show up in the system databases tree in the Object Explorer pane of SQL Server Management Studio, and it will not appear in the drop-down list of databases accessible from your query windows. However, this database still needs disk space.

You can see the files in your default data directory by using Windows Explorer. My data directory is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data; I can see a file called mssqlsystemresource.mdf, which is 38 MB in size, and mssqlsystemresource.ldf, which is 0.5 MB. The created and modified date for both of these files is the day I installed this SQL Server instance, but their last accessed date is today.

If you have a burning need to "see" the contents of mssqlsystemresource, a couple of methods are available. The easiest, if you just want to see what's there, is to stop SQL Server, make copies of the two files for the resource database, restart SQL Server, and then attach the copied files to create a database with a new name. You can do this by using Object Explorer in SQL Server Management Studio or by using the CREATE DATABASE FOR ATTACH syntax to create a clone database, as shown here:

CREATE DATABASE resource_COPY  ON (NAME = data, FILENAME =     'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ mssqlsystemresource_COPY.mdf'), (NAME = log, FILENAME =     'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource_COPY.ldf')     FOR ATTACH;

SQL Server will treat this new resource_COPY database like any other user database, and it will not treat the objects in it as special in any way. If you want to change anything in the resource database, such as the text of a supplied system stored procedure, changing it in resource_COPY will obviously not affect anything else on your instance. However, if you start your SQL Server instance in single-user mode, you can make a single connection, and that connection will be able to use the mssqlsystemresource database. Starting an instance in single-user mode is not the same thing as setting a database to single-user mode. For details on how to start SQL Server in single-user mode, see the SQL Server Books Online entry for the Sqlservr.exe application. In Chapter 6, when I discuss database objects, I'll discuss some of the objects in the resource database.

msdb

The msdb database is used by the SQL Server Agent service, which performs scheduled activities such as backups and replication tasks, and the Service Broker, which provides queuing and reliable messaging for SQL Server. When you are not performing backups and maintenance on this database, you should generally ignore msdb. (But you might take a peek at the backup history and other information kept there.) All the information in msdb is accessible from Object Explorer in SQL Server Management Studio, so you usually don't need to access the tables in this database directly. You can think of the msdb tables as another form of system tables: Just as you can never directly modify system tables, you shouldn't directly add data to or delete data from tables in msdb unless you really know what you're doing or are instructed to do so by a Microsoft SQL Server technical support engineer. Prior to SQL Server 2005, it was actually possible to drop the msdb database; your SQL Server instance was still usable, but you couldn't maintain any backup history, and you weren't able to define tasks, alerts, or jobs, or set up replication. In SQL Server 2005, there is an undocumented traceflag that allows you to drop the msdb database, but because the default msdb database is so small, I recommend leaving it alone even if you think you might never need it.