Introduction
A Microsoft SQL Server database is a collection of objects that hold and manipulate data. A typical SQL Server installation has only a handful of databases, but it’s not unusual for a single installation to contain several dozen databases. Theoretically, one SQL Server installation can have as many as 32,767 databases. But practically speaking, this limit would never be reached. SQL Server has 6 Built In databases that provide Template Databases, Hold System Information and Backup Information etc. The details of each database is given below.
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, usage, systemwide configuration settings, login accounts, the existence of other databases, and the existence of other SQL servers (for distributed operations). The master database is absolutely critical to your system, so be sure to 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 activities, 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.
TEMPDB
The temporary database, tempdb, is a workspace. SQL Server’s tempdb database is unique among all other databases because it’s re-created—not recovered—every time SQL Server is restarted. It’s used for temporary tables explicitly created by users, for worktables to hold intermediate results created internally by SQL Server during query processing and sorting, and for the materialization of static cursors and the keys of keyset cursors. Operations within tempdb are logged so that transactions on temporary tables can be rolled back, but the records in the log contain only enough information to roll back a transaction, not to recover (or redo) it. No recovery information is needed because every time SQL Server is started, tempdb is completely re-created; any previous user-created temporary objects (that is, all your tables and data) will be gone. Logging only enough information for rolling back transactions in tempdb was a new feature in SQL Server 7 and can potentially increase the performance of INSERT statements to make them up to four times faster than inserts in other (fully logged) databases.
All users have the privileges to create and use private and global temporary tables that reside in tempdb. However, by default, users don’t have the privileges to USE tempdb and then create a table there (unless the table name is prefaced with # or ##). But you can easily add such privileges to model, from which tempdb is copied every time SQL Server is restarted, or you can grant the privileges in an autostart procedure that runs each time SQL Server is restarted. If you choose to add those privileges to the model database, you must remember to revoke them on any other new databases that you subsequently create if you don’t want them to appear there as well.
PUBS
The pubs database is a sample database used extensively by much of the SQL Server documentation. You can safely delete it if you like, but it consumes only 2 MB of space, so unless you’re scrounging for a few more megabytes of disk space. This database is admittedly fairly simple, but that’s a feature, not a drawback. The pubs database provides good examples without a lot of peripheral issues to obscure the central points. Another nice feature of pubs is that it’s available to everyone in the SQL Server community, which makes it easy to use to illustrate examples without requiring the audience to understand the underlying tables or install some new database to try out your examples. You can completely rebuild the pubs database from scratch by running a script in the \Install subdirectory (located right under the SQL Server installation directory). In SQL Query Analyzer, open the file named Instpubs.sql and execute it. You do need to make sure that there are no current connections to pubs, because the current pubs database is dropped before the new one is created.
NORTHWIND
The Northwind database is a sample database that was originally developed for use with Microsoft Access. Much of the documentation dealing with APIs uses Northwind, as do some of the newer examples in the SQL Server documentation. It’s a bit more complex than pubs, and at almost 4 MB, slightly larger. As with pubs, you can safely delete Northwind if you like, although the disk space it takes up is extremely small compared to what you’ll be using for your real data. It is recommend leaving Northwind there. The Northwind database can be rebuilt just like the pubs database, by running a script located in the \Install subdirectory. The file is called Instnwnd.sql.
MSDB
The msdb database is used by the SQL Server Agent service, which performs scheduled activities such as backups and replication tasks. In general, other than performing backups and maintenance on this database, you should ignore msdb. All the information in msdb is accessible from the SQL Server Enterprise Manager tools, so you usually don’t need to access these tables directly. Think of the msdb tables as another form of system tables: just as you should 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.
Thanks,
Vijay Kumar (Win)
|
| Author: Satya Prasad 24 Nov 2005 | Member Level: Silver Points : 0 |
You posted a very great article. Keep it up. I hope you will send some more articles like this.
|