Introduction of System Databases in SQL Server 2008

Today I saw one question in Experts-Exchange.com which inspires me to write this introduction of system databases. Question asker has used Master database to create his tables for his application, as time passed, application became big and was using so many tables which he has generated in Master database.  Somebody has updated him not to use Master database for user tables than he came to know what mistake he did and raised the question in forum that how can he decided what table belongs to system and what table belongs to his application and how can he move those tables.

This kind of situation may occur if you are not SQL Server expert and you don’t know what the system database is and how it is useful.

Anyway, let me come back to the point of system database in SQL Server 2008. Basically there are four most important system databases which you shouldn’t touch for creating your own user tables, views etc. and also afraid to change any table and/or information in such databases.

Here is the list of four most important system databases which ships with Microsoft SQL Server 2008 by default.

1.)    Master

2.)    Model

3.)    MSDB

4.)    TempDB

Let us look a brief description of each database.

Master Database:  Master is one of the very crucial databases. No Master database No SQL Server J it is composed of system tables that keep track of your whole SQL Server and its installation along with all databases which will be going to created in the server.  It also keeps records of your disk space, system wide configuration, file allocation, logins and existence of other databases to name a few.  So as long as possible, don’t make any change in MASTER database and keep a latest backup copy always with you.

Model Database:  Model database has its own importance. You can say it a template of all other databases. It has a standard set of objects within it so whenever you create any new database in your server, all objects from Model database will be inherited so if you want your new database to get generated with some default objects or permission, put it in Model Database and you are done.

MSDB Database:  MSDB database is being used by SQL Server agent which used to handle all schedule jobs for you.

TempDB Database: TempDB database has really interesting behavior. It used to regenerate every time you restart your SQL Server service. It is basically a workspace and temporary table (global and local both) created by user, used to get stored in tempDB by default.  Some intermediate operation like query processing and sorting also use TempDB database so it would be beneficial to keep TempDB on separate and fast drive in order to get performance.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com