Thursday, June 25, 2015

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.

No comments:

Post a Comment