Lesson 7: System Databases — master / model / msdb / tempdb (Do’s and Don’ts)

Section 6 — Lesson 7: System Databases — master / model / msdb / tempdb (Do’s and Don’ts)

Welcome back. If a user database fails, you may lose one application.
If a system database is damaged or mismanaged, you can lose the entire SQL Server instance.
That is why DBAs treat system databases with a higher level of discipline.

In this lesson, you will learn what each system database is responsible for,
what you should and should not change, and the practical DBA behaviors that prevent instance-wide outages.

Lesson description: You will learn the purpose of master, model,
msdb, and tempdb, what critical metadata each contains, and what breaks if they are corrupted.
You will learn safe DBA practices (backups, monitoring, access control), and unsafe practices to avoid
(creating user objects in system DBs, careless recovery model changes, shrinking, and uncontrolled growth).
You will also run safe scripts to inventory system database configuration and verify backup posture.

1) What System Databases Are (Big Picture)

Every SQL Server instance includes system databases that store configuration and operational metadata.
The four core system databases you must know are:

• master — the instance “brain”: instance-level metadata and configuration anchors
• model — the template used when new databases are created
• msdb — the operations database: SQL Agent jobs, backup history, alerts, etc.
• tempdb — the workspace database: temporary objects and internal work

DBA rule: System databases are not “regular” databases. Treat them like production infrastructure.

2) master Database (Purpose, Do’s and Don’ts)

2.1 What master is used for

The master database stores critical instance-wide metadata. Examples include:

• information about databases on the instance (basic registration/metadata)
• server-level security principals (logins) and related metadata
• server configuration references and instance-level settings metadata
• startup information that SQL Server needs to initialize databases
• metadata that supports features like linked servers (and other server-scoped objects)

If master is corrupted or missing, SQL Server may not start normally.

2.2 master DO’s

• Back up master after major instance-level changes (new logins, configuration changes, patches, etc.).
• Secure master tightly (least privilege; avoid casual access).
• Monitor master file growth and keep it stable.
• Document instance-level changes (change logs) so master changes are explainable and reversible.

2.3 master DON’Ts

• Do not create user tables or app objects in master. Ever.
• Do not use master as an “admin scratchpad.” Use a dedicated DBA utility database if needed.
• Do not shrink master as routine maintenance.
• Do not change master recovery settings without a clear operational reason.

3) model Database (Purpose, Do’s and Don’ts)

3.1 What model is used for

The model database is the template SQL Server uses whenever you create a new database.
Any setting or object in model may be copied into newly created databases.

Examples of what model influences:

• default file growth settings for new databases
• default database options (depending on version and settings)
• any objects you create in model (tables, procedures, etc.) can appear in new databases

3.2 model DO’s

• Keep model clean and minimal.
• Use model intentionally if you have a standardized new-database template policy (advanced DBA practice).
• Ensure model is sized and configured appropriately (not tiny growth that causes constant expansions).

3.3 model DON’Ts

• Do not add random objects to model. They can pollute future databases and create security risk.
• Do not ignore model growth settings. Bad defaults get copied everywhere.
• Do not treat model as a regular user database.

DBA warning: If someone adds objects or settings to model “just once,”
you may be fighting weird behavior in every new database for years.

4) msdb Database (Purpose, Do’s and Don’ts)

4.1 What msdb is used for

The msdb database supports SQL Server operations and automation.
It commonly stores:

• SQL Server Agent jobs, schedules, operators, and alerts
• backup and restore history
• maintenance plan metadata (if used)
• Database Mail configuration metadata (varies by setup)
• some SSIS metadata and other operational records (depends on components installed)

If msdb is unhealthy, you may lose job schedules, backup history, alerting, or automation.
DBAs treat msdb as “production-critical.”

4.2 msdb DO’s

• Back up msdb regularly (often daily) because jobs and schedules matter.
• Secure msdb: Agent roles, job ownership, and permissions should be controlled.
• Monitor msdb growth (backup history tables can grow fast if not maintained).
• Clean up history in a controlled way (retain needed compliance evidence).

4.3 msdb DON’Ts

• Do not let msdb grow forever due to unlimited history retention.
• Do not store application tables in msdb.
• Do not make everyone a SQL Agent admin. That can become a security incident.

DBA reality: During migrations, people copy databases but forget msdb content
(jobs/operators/alerts). That is why DBAs document and migrate msdb operational objects intentionally.

5) tempdb Database (Purpose, Do’s and Don’ts)

5.1 What tempdb is used for

tempdb is SQL Server’s shared workspace, used for:

• temporary tables and internal worktables
• sorting and hashing spills when memory grants are insufficient
• row versioning (snapshot-based isolation patterns)
• various internal engine operations under concurrency

tempdb is recreated at startup, so “restoring tempdb” is not a thing.
Instead, DBAs configure it correctly and monitor it.

5.2 tempdb DO’s

• Pre-size tempdb and use sensible growth settings.
• Keep tempdb data files equal size with equal growth settings (common best practice).
• Place tempdb on fast storage whenever possible.
• Monitor tempdb I/O and contention evidence.

5.3 tempdb DON’Ts

• Do not shrink tempdb routinely. It will typically regrow and can fragment.
• Do not ignore tempdb growth events. That often indicates spills, bad queries, or increased workload.
• Do not put tempdb on the slowest shared disk. It becomes a server-wide bottleneck.

6) One Table: What Breaks If Each System Database Is Damaged?

DBA takeaway: System database health is instance health.

7) Evidence: Inventory System Database Settings (Safe Queries)

Use these scripts to inspect configuration. They are read-only.

7.1 List system databases and critical settings

SELECT
    name,
    database_id,
    state_desc,
    recovery_model_desc,
    is_read_only,
    is_auto_close_on,
    is_auto_shrink_on,
    compatibility_level
FROM sys.databases
WHERE name IN ('master','model','msdb','tempdb')
ORDER BY database_id;

7.2 List system database files and sizes

SELECT
    DB_NAME(database_id) AS DatabaseName,
    file_id,
    name AS LogicalFileName,
    type_desc,
    size * 8 / 1024 AS SizeMB,
    growth,
    is_percent_growth,
    physical_name
FROM sys.master_files
WHERE DB_NAME(database_id) IN ('master','model','msdb','tempdb')
ORDER BY DatabaseName, type_desc, file_id;

8) Backup Discipline for System Databases (What DBAs Actually Do)

Here is the job-ready mental model:

• master: back up after significant instance-level changes (logins/config/patches). Often daily in production.
• msdb: back up regularly because it contains job schedules and operational metadata. Often daily.
• model: back up when changed (and keep it clean so changes are rare).
• tempdb: not backed up (recreated on restart). Focus on configuration and monitoring.

DBA note: Many environments include system database backups in the same automated backup framework as user databases,
with exceptions for tempdb.

9) The Most Dangerous “New DBA” Mistakes (Avoid These Always)

• Creating objects in master/model/msdb for convenience
• Shrinking system databases as a “maintenance step”
• Letting msdb grow forever due to unbounded history
• Ignoring tempdb configuration and later blaming SQL Server for “random slowdowns”
• Changing recovery model without understanding restore requirements and log backup strategy

Practice (Required)

1. Run the system database settings query (7.1) and save output to:
   C:\DBA\Logs\section6-lesson7-systemdb-settings.txt
2. Run the system database files query (7.2) and save output to:
   C:\DBA\Logs\section6-lesson7-systemdb-files.txt
3. In your DBA notebook, write 8–10 sentences answering:
   • What is master responsible for? Why is it critical?
   • Why should model be kept clean?
   • What operational items live in msdb?
   • Why is tempdb not backed up, and how do DBAs protect it instead?

Lesson Summary

• master is instance-critical metadata; protect and back it up.
• model is the template for new databases; keep it minimal and intentional.
• msdb powers automation and history (Agent jobs/alerts/backup history); back it up and control growth.
• tempdb is the shared workspace; configure it well and monitor it because it can bottleneck the whole instance.

Next Lesson: Lab — Inspect Internal Metadata (DMVs Introduction Safely)

Next, you will learn how DBAs use DMVs (Dynamic Management Views) safely to observe what SQL Server is doing in real time.
You will build a small “internals snapshot” script pack you can reuse during troubleshooting.