Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 15, 2000

System Databases and Tables

By Krishnan Kaniappan

When you install SQL Server 7.0, the setup program automatically creates four system databases along with two sample user-defined databases i.e. Pubs and Northwind. The four system databases that play a vital role in the successful operation of SQL Server are Master, Model, Msdb and Tempdb database.

Master Database:

This database stores all critical information such as server specific configuration information, user login accounts, running processes, system error messages, system stored procedures etc. It also records the existence of other databases and the location of the primary files that contain the initialization information for the user databases. If this database is corrupted then you can use the utility rebuildm.exe to rebuild the master database and then restore the other user databases accordingly. So it.s always preferable to have a recent backup of this database.

The system tables in MASTER database can be categorized as follows:

REMOTE SERVICESACTIVE USER PROCESSESCURSORS

sysserverssysprocessessyscursorcolumns

sysremoteloginssyslockinfosyscursortables

sysoledbuserssyslockssyscursors

sysloginssyscursorrefs

sysxlogins

CONFIGURATIONSTORAGE OTHERS

sysconfiguressysdevicessyslanguages

syscurconfigssysaltfilessysmessages

syscurconfigs

sysdatabases

syscacheobjects

syscharsets

More information about these tables can be found in BOL.

Model Database:

This database acts as a template for the new databases i.e. whenever you create a new database the database objects present in this database get copied onto the new database. For example if you create a new database without specifying any size i.e. Create database <d/b name> statement then SQL allocates this database the default size of model d/b i.e. 0.75 MB so that the objects in the model database are copied onto the new database. If you want to perform some common operations on all user databases then you can include those objects such as tables, procedures etc so that the new database also contains them. Some of the common operations performed in the model database are:

->Inclusion of user-defined datatypes, constraints, rules or defaults etc.

->Inclusion of users who have permissions on all the databases.

->Setting of database configuration options etc.

The following are the system tables present in MODEL and all databases:

USERS & PERMISSIONSOBJECTSREFERENTIAL INTEGRITYSTORAGE

sysmemberssysfulltextcatalogssysforeignkeyssysfiles1

sysprotectssyscommentssysreferencessysfilegroups

sysuserssysobjectssysindexkeyssysfiles

syspermissionssysdependssysallocations

sysconstraints

sysindexes

syscolumns

systypes

More information about these tables can be found in BOL.

Msdb Database:

This is one of the system databases that play an important role in SQL server's management and maintenance. It contains some system defined tables that are specific to this database only. The Msdb database contains task-scheduling, exception handling, alert management and system operator information i.e. it holds the information of all the operators such as email addresses or pager numbers and history information about all the backups and restore operations.

The system tables in MSDB database can be categorized as follows:

BACKUPSQL SERVER AGENTDTSDATABASE MAINTENANCE

backupsetsysalertssysdtspackagessysdbmaintplans

backupfilesysnotificationssysdtscategoriessysdbmaintplan_databases

backupmediasetsysoperatorssysdbmaintplan_history

backupmediafamilysysjobhistorysysdbmaintplan_jobs

restorehistorysysjobsteps

restorefilegroupsyscategories

restorefilesystaskids

sysjobs

sysjobschedules

sysdownloadlist

systargetservergroups

systargetservergroupmembers

systargetservers

sysjobservers

More information about these tables can be found in BOL.

Tempdb Database:

As the name suggests this database stores all the temporary tables and the temporary results generated by group by, order by, distinct clause etc. The temporary data resides on the tempdb database till the user is connected to the SQL server. All the temporary data and tables related to the user get dropped once the user logs out from the SQL Server. All the global variables lose their values after SQL Server shuts down or SQL Server crashes. Tempdb auto grows as needed and each time the system is started; tempdb is reset to its default size (i.e. 8.0 MB for data file and 0.5MB for log file). Because Tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.

Unlike the earlier versions of SQL Server, the databases are not stored on the devices but they are stored directly in the files that can grow independently of each other so now there is no restriction of having master and model database in a single file (i.e. master device w.r.t earlier version) which sometimes caused problems with space. But in SQL Server 7.0 you have no problems of space because all of these databases have their own set of files that can grow independently of each other.

Note:

You should not modify system tables directly unless you know the architecture of all the system tables. Microsoft recommends not to modify system tables directly but to retrieve information in the system tables using system stored procedures, T-SQL functions, information schema views etc. because the successful operation of SQL Server depends on the integrity of information in the system tables. If you look deep into the system tables there is no integrity enforced i.e. for example if you just execute sp_help <system table name> then you will find that there will be no foreign key and references constraint defined on them.

And also applications should not be written to query the system tables directly because the format of the system tables is dependent upon the internal architecture of SQL Server and may change from release to release so applications that directly access the system tables may have to be changed before they can access a later version of SQL Server. Therefore it is always preferable not to modify the system tables directly.

If you want to make any suggestions regarding this article you can e-mail me at the following address: l_muthu@hotmail.com



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM