System Databases and Tables


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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles