MS SQL System Databases

SQL Instances:

Each instance of MS-SQL is a unique instance and shares nothing with the other instances residing on the same or different server. If more than once instances are running on the same server only one can be the default instance. In order to connect to a named instance you have to specify the name of the server and the name of the instance.

DATABASES in MS SQL
Are a collection of tables, stored procedures, triggers, views and so on.  One instance of a SQL Server can operate several databases.  Upon install MS_SQL creates several databases that it needs for its operations such as:

  • Master: It mainly holds wide system metadata information for the SQL instance, system configuration setup information, account login details, information aboutthe databases running under this instance and so on.
  • Resource: It holds system objects and it is read only. It contains all systems objects. These system objects are the actual system databases MS SQL uses to function and they are physically stored in the resource database. However they appear logically in the sys schema on all databases. This database is hidden but the data associated with it appear as if they are part of the selected user database. The resource database is used to store all system views, stored procedures. It does not have any information about user data/metadata. This database is also used to make up upgrade of MS SQL from one version to another much easier.
  • model: This is the model database. When a new database is created this database is being used as a template to set up default settings for newly created databases. in order to view the value settings for this database you will need to use the sys.database catalog view. You can navigate the sys.databases view in Management Studio under “System Databases – msdb – System Views –  sys.databases”.  Here is a list of settings the model database has under its command.

It is worth mentioning at this point that the sys.databases view has some very interesting information regarding not only  the model database but also regarding the overall status of a database/databases with in an instance. Not all information in the sys.databases view is related to the model database. Below  is a description of some of the information the sys.databases view offers. A more full description can be found here. You can do a query  on any of the following fields below; for example SELECT name from sys.database

    • NAME:  Name of the database attached to this particular instance.
    • DATABASE_ID: Each database has a unique ID within an instance and it is listed here.
    • OWNER_SID: This is the “Security Identifier” for the external database as it is registered to the server.
    • COMPATIBILITY_LEVEL: This level shows the level of backward compatibility a database has. For example if a database is created with in SQL 2008 R2 the compatibility level will be set as 100. If you have upgraded from SQL 2005 to SQL 2008 R2 the compatibility level will be set as 90. If you have upgraded from SQL 2000 to SQL 2008 R2 the compatibility level will be set to 80.
    • USER_ACCESS and USER_ACCESS_DESC:  It shows if a database is single/multiuser/restricted user specified.   0 = multiuser,   1=single,  2=restricted user specified
    • IS_REAL_ONLY:  It shows if a database is in read user mode or not.
    • IS_AUTO_SHRINK_ON: It shows if the database has/will have the autoshrink option set to on or off.
    • STATE: It describes the state a database. 0 = online, 1= Restoring, 2=Recovering, 3=Recovery_Pending, 4=Suspect, 5=Emergency, 6=Offline. For a full list of these states visit here
    • IS_CLEANLY_SHUTDOWN: Shows if a database was cleanly shut down or not.
    • RECOVERY_MODEL_DESC: Shows the recovery mode a database has/will have. These can be FULL, BULK_LOGGED, SIMPLE.
    • IS_ENCRYPTED: Shows if a database is/will be encrypted or not
  • tempdb: As the name implies this is a temporary database. The contents of this database are lost each time the SQL service is restarted. Here users/applications/services can create temporary tables to store data. The engine it self also creates work tables among other things. Each SQL instance has its own tempdb database. The size of this database is set to unrestricted by default.
  • msdb: Is the database where all data from the SQL Server Agent is stored. This is data regarding automated tasks such as jobs, schedules, alerts,  email notifications and it also contains information regarding replication.
  • Resource: It holds system objects and it is read only. These system objects are the actual system databases MS SQL uses to function and they are physically stored in the resource database. However they appear logically in the sys schema on all databases. The resource database is used to store all system views, stored procedures. It does not have any information about user data/metadata. This database is also used to make up upgrade of MS SQL from one version to another much easier.
Share Button

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload the CAPTCHA.