System Databases in Microsoft SQL Server: An In-Depth Overview

For a seasoned MSSQL DBA, understanding the intricacies of system databases is critical for performance tuning, disaster recovery, and maintaining system integrity. Below is a comprehensive breakdown of each system database, including their roles, internal structures, best practices, and common challenges.

1. MASTER Database

• Purpose:
  • The central repository for instance-wide metadata and configuration.
  • Tracks logins, linked servers, endpoints, system configurations, and file locations of all databases.
  • Critical for instance startup (SQL Server cannot start if master is corrupted).
• Key Contents:
  • System catalogs: sys.databases, sys.server_principals, sys.configurations.
  • Service Broker and replication metadata.
  • Instance-level DDL triggers and encryption keys (Database Master Key).
• Best Practices:
  • Backup: Full backups after significant configuration changes (e.g., adding logins, altering endpoints).
  • Disaster Recovery: Keep a script of server-level objects (logins, jobs) to rebuild master if needed.
  • Security: Restrict access to sysadmin roles; avoid user object creation here.
• Common Issues:
  • Corruption: Requires rebuilding via setup.exe /ACTION=REBUILDDATABASE or restoring from backup.
  • File Relocation: Use SQL Server Configuration Manager to move master data/log files.

2. MODEL Database

• Purpose:
  • Template for new user databases.
  • Defines default settings (e.g., recovery model, file growth, collation) inherited by new databases.
• Key Customizations:
  • Set default recovery model (FULL, SIMPLE, BULK_LOGGED).
  • Pre-create objects (tables, stored procedures) to propagate to new databases.
  • Configure database options (e.g., AUTO_CLOSE, PAGE_VERIFY).
• Best Practices:
  • Modify model to enforce organizational standards (e.g., SIMPLE recovery for staging DBs).
  • Avoid excessive size or unnecessary objects to prevent bloat in new databases.
• Common Issues:
  • Misconfiguration: Incorrect settings (e.g., AUTO_SHRINK) leading to performance issues in new DBs.
  • File Sizes: Initial data/log file sizes are set in model; adjust to avoid frequent auto-growth.

3. MSDB Database

• Purpose:
  • Central hub for automation and maintenance tasks:
    • SQL Server Agent (jobs, alerts, operators).
    • Backup/restore history (backupset, backupmediafamily tables).
    • SSIS package storage (if deployed to server).
    • Database Mail and Policy-Based Management.
• Key Tables:
  • sysjobs, sysjobhistory, sysalerts, sysmail_* tables.
  • dbo.log_shipping_* for log shipping configurations.
• Best Practices:
  • Backup: Schedule regular backups to retain job definitions and history.
  • Cleanup: Use sp_delete_backuphistory or purge policies to manage table growth.
  • Security: Restrict access to prevent unauthorized job modifications.
• Common Issues:
  • Space Growth: Uncontrolled job history retention leading to large tables.
  • SSIS Storage: Deploying excessive packages to msdb impacts performance.

4. TEMPDB Database

• Purpose:
  • Shared workspace for transient data:
    • Temporary tables, table variables, cursors.
    • Version Store (for READ_COMMITTED_SNAPSHOT, SNAPSHOT_ISOLATION).
    • Internal objects (worktables for sorting, hashing, spooling).
• Key Characteristics:
  • Recreated on SQL Server restart; configured via model settings.
  • Single instance-wide tempdb with multiple data files recommended for scalability.
• Best Practices:
  • Configuration:
    • Pre-size data files to avoid auto-growth (e.g., 8GB per file).
    • Align the number of data files with CPU cores (1:1 up to 8 cores).
  • Monitoring: Track PAGELATCH waits (use DMVs like sys.dm_os_wait_stats).
  • IO Optimization: Place on fast storage (SSD) with dedicated disks.
• Common Issues:
  • Contention: PAGELATCH_EX waits due to allocation bitmap (SGAM/PFS) contention.
  • Version Store Bloat: Long-running transactions causing excessive version retention.

5. RESOURCE Database

• Purpose:
  • Hidden, read-only store for system objects (e.g., sys.objects, DMVs).
  • Logical presence in every database’s sys schema.
  • Updated during SQL Server patches/upgrades (replaces mssqlsystemresource files).
• Key Details:
  • Location: \<SQL Server install dir>\MSSQL\Binn\.
  • Recovery: Requires reinstalling SQL Server or restoring from installation media.
  • Visibility: Accessible via DAC (Dedicated Admin Connection) or file system.