What are the SQL Server System databases? — Parv The IT Geek

SQL Server System databases

SQL Server has 5 very default system databases which are

Master

As the name implies this is one of the most important databases on the system and without it SQL Server will not start. It stores all the configuration of the instance and all other databases which are subsequently created. The information is stored into tables known as system tables.

MSDB

The MSDB database is used by SQL Agent and contains data about jobs, job schedules, alerts, log shipping, database mail, recovery of damaged pages….. This information is retrievable through the tables stored within this database.

Model

This is basically a template database for the creation of new databases.

TempDB

TempDB is recreated every time SQL is restarted and is basically used for storing of information temporarily when SQL Server doesn’t have sufficient memory available. This could be a sorting query, temporary tables, holding variables…..

Resource

This is a hidden read-only database which contains information on all objects within SQL server. If you look in the directory of your SQL Server installation you will find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf which contain the resource database. The hidden nature of the database makes it easy to upgrade via hot-fixes and service packs.


Originally published at https://parvtheitgeek.com on January 16, 2014.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: