TSQL Tutorials : System Databases

The master Database

Every SQL Server, regardless of version or custom modifi cations, has the master database. This

database holds a special set of tables (system tables) that keeps track of the system as a whole.

For example, when you create a new database on the server, an entry is placed in the sysdatabases table

in the master database. All extended and system-stored procedures, regardless of which database

they are intended for use with, are stored in this database.

Obviously, since almost everything that describes your server is stored in here, this database is critical to

your system and cannot be deleted.

The system tables, including those found in the master database, were, in the past, occasionally

used in a pinch to provide system confi guration information, such as whether certain objects existed

before you performed operations on them.

Microsoft warned developers for years not to use the system tables directly, but, because there were few

other options, most developers ignored that  advice. Happily, Microsoft began providing other options in the

form of system and information schema views; you can now utilize these views to get at the systems’

metadata as necessary, with Microsoft’s full blessing.

For example, if you try to create an object that already exists in any

particular database, you get an error. If you want to force the issue, you could test to see whether

the table already has an entry in the sys.objects table for that database. If it does, you would

delete that object before re-creating it.

The model Database

The model database is aptly named, in the sense that it’s the model on which a copy can be based. The

model database forms a template for any new database that you create. This means that you can, if

you want, alter the model database if you want to change what standard, newly created databases look

like. For example, you could add a set of audit tables that you include in every database you build. You

could also include a few user groups that would be cloned into every new database that was created

on the system. Note that because this database serves as the template for any other database, it’s a

required database and must be left on the system; you cannot delete it.

There are several points to keep in mind when altering the model database:

Any database you create has to be at least as large as the model database. That means that

if you alter the model database to be 100MB in size, you can’t create a database smaller

than 100MB.

Similar pitfalls apply when adding objects or changing settings, which can lead to unintended

consequences. As such, for 90 percent of installations, I strongly recommend leaving

this one alone.

The msdb Database

msdb is where the SQL Agent process stores any system tasks. If you schedule backups to run on a

database nightly, there is an entry in msdb. Schedule a stored procedure for one-time execution, and yes,

it has an entry in msdb. Other major subsystems in SQL Server make similar use of msdb. SSIS packages

and policy-based management defi nitions are examples of other processes that make use of msdb.

The tempdb Database

tempdb is one of the key working areas for your server. Whenever you issue a complex or large

query that SQL Server needs to build interim tables to solve, it does so in tempdb. Whenever you

create a temporary table of your own, it is created in tempdb, even though you think you’re creating

it in the current database. (An alias is created in the local database for you to reference it by, but the

physical table is created in tempdb.) Whenever there is a need for data to be stored temporarily, it’s

probably stored in tempdb.

tempdb is very different from any other database. Not only are the objects within it temporary, the

database itself is temporary. It has the distinction of being the only database in your system that is

rebuilt from scratch every time you start your SQL Server.