SQL Server System Databases: master, model, msdb, tempdb and Resource

Every SQL Server instance comes with system databases it needs to run, alongside the user databases. They’re often ignored, until msdb grows to gigabytes and fills the disk, a setting in the model database bloats the log of every new database, or a rebuilt server turns out to have lost every login because master was never backed up.

This guide covers what each system database holds, how to back it up and the most common problems.

The System Databases and Their Roles

master

SQL Server doesn’t start without master. Everything at server level lives here: logins and their passwords, server roles, sp_configure settings, linked servers, endpoints and where each user database is.

Avoid adding user tables or procedures to master. These objects are easily forgotten during migrations and make restoring master harder.

model

Every new database starts as a copy of model: its size, growth settings, recovery model and any objects added to model carry over to the new database. TempDB is also created from model at every startup.

model’s default recovery model is FULL, so new databases start in FULL too. If no log backups are planned for them, the transaction log grows endlessly. This is one of the most common reasons log files fill disks in the field. Either set model’s recovery model to your organization’s standard, or make it a rule to add every new database to the backup plan. Details are in our recovery model guide.

msdb

The SQL Server Agent database: jobs, schedules, operators, alerts, Database Mail and backup and restore history live here. Backup and job history that’s never purged can grow msdb to gigabytes over time and slow down tools that query backup history.

-- Purge backup and restore history older than 90 days (in a weekly job)
DECLARE @oldest datetime = DATEADD(DAY, -90, GETDATE());
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest;
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @oldest;

Without an msdb backup, rebuilding the server loses every Agent job, maintenance plan and alert. See our Agent guide for managing Agent.

tempdb

Temporary tables, table variables, spills from sort and hash operations, row versioning and index operations use tempdb. Because it’s recreated from scratch at every startup, it isn’t backed up, but it’s the most performance-critical system database. See our TempDB guide for file count, size and placement.

Resource

A hidden, read-only database (mssqlsystemresource.mdf) holding the definitions of system objects such as sys.objects. It doesn’t appear in Management Studio and isn’t backed up with BACKUP. Cumulative updates update this file; it shouldn’t be touched by hand.

Checking Their State

SELECT d.name, d.recovery_model_desc,
       CAST(SUM(mf.size) * 8 / 1024.0 AS decimal(12, 1)) AS size_mb,
       (SELECT MAX(b.backup_finish_date) FROM msdb.dbo.backupset AS b
        WHERE b.database_name = d.name AND b.type = 'D') AS last_full_backup
FROM sys.databases AS d
JOIN sys.master_files AS mf ON mf.database_id = d.database_id
WHERE d.database_id <= 4
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

Checklist

• master and msdb are backed up regularly; model too if customized.
• No user objects in master.
• model’s recovery model and growth settings match the organization’s standard.
• Backup and job history in msdb is purged regularly; its size is monitored.
• tempdb is sized correctly and on a separate disk.
• The restore procedure for system databases is documented and tested.