SQL Server System Databases

The databases that are loaded with SQL Server are known as system databases. These are used to store system-level data, metadata data, data about scheduled jobs and alerts, etc.

There are five system databases; four of them will be shown in the object explorer.

master Database

Records all the system-level information for an instance of SQL Server.

msdb Database

is used by SQL Server Agent for scheduling alerts and jobs.

model Database

is used as the model for all databases built on the SQL Server instance. All subsequent databases are established with the same settings as the model database, including database size, collation, recovery model, and other database variables.

Resource Database

contains system objects that come with SQL Server and is a read-only database. System objects are logically present in every database’s sys schema, but they are physically persisted in the Resource database. User data and user metadata are not present in the Resource database.

tempdb Database
is a workspace for holding temporary objects or intermediate result sets.

Please be aware that SQL Server does not permit direct user updates to system objects including system tables, system stored procedures, and catalog views. Instead, SQL Server offers a full suite of administrative tools that let users control all users and items in a database and completely administer their system.

Hope you find this article helpful.

One comment

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 )

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