Sql Server System Databases Complete Guide

Introduction to SQL Server System Databases

SQL Server comes with several system databases that it uses for its operation. These databases store information critical to the operation and management of SQL Server itself, and they should not be modified directly without proper understanding and caution. The main system databases are:

1. master: Contains global data and information about all other databases.
2. model: Template database used as the basis for all new databases created on the server.
3. msdb: Stores administrative information required by SQL Server Agent and some of the SQL Server maintenance tasks.
4. tempdb: Temporary storage area for objects like temporary tables and stored procedures.
5. resource: Hidden read-only database that contains definitions of all system objects available in SQL Server.

Let’s look at examples of how to interact with these databases.

Step 1: Connect to SQL Server

First, open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

Step 2: View Master Database Objects

The master database contains system objects and metadata about all other databases. To view the schemas and tables within master, you can use:

USE master;
GO

SELECT schema_name(schema_id), name 
FROM sys.tables;

Example Explanation:

• USE master;: Switches the context to the master database.
• The query selects the schema name and table names from sys.tables.

Step 3: Inspect Model Database Settings

The model database is a template for all new user databases created on the server. You can inspect its settings using:

USE model;
GO

EXEC sp_helpfile;

Example Explanation:

• USE model;: Switches the context to the model database.
• sp_helpfile stored procedure lists all the files associated with the model database, including the initial file size and growth settings.

Step 4: Review Backup Information Using msdb

The msdb database stores information required by the SQL Server Agent and various maintenance plans and jobs run on the server. Here's how you can review the backup history:

USE msdb;
GO

SELECT 
    CONVERT(datetime, bs.backup_start_date) AS BackupStartDate,
    bs.database_name AS DatabaseName, 
    bs.backup_type AS BackupType,
    bf.device_type AS BackupDeviceType,
    bf.physical_device_name AS BackupLocation
FROM 
    backupset bs 
INNER JOIN 
    backupmediafamily bf 
ON 
    bs.media_set_id = bf.media_set_id 
ORDER BY 
    bs.backup_start_date DESC;

Example Explanation:

• USE msdb;: Switches the context to the msdb database.
• This query joins backupset and backupmediafamily tables to get details about the most recent backups (ordered by the backup_start_date).

Step 5: Query Temporary Tables in tempdb

The tempdb database is a workspace for temporary storage of objects like tables, indexes, stored procedures, and more. Here’s how to check the temporary tables currently present in tempdb:

USE tempdb;
GO

SELECT name, type_desc 
FROM sys.objects 
WHERE type IN ('U', 'S') -- U: User Tables, S: System Tables
AND is_ms_shipped = 0; 

Example Explanation:

• USE tempdb;: Switches the context to the tempdb database.
• The query fetches names and types of non-system user objects (tables and stored procedures).

Step 6: Examine Resource Database Objects

The resource database is hidden and is used to store system-critical objects like system-defined tables, messages, and views. Although direct modifications are not allowed, you can see what it contains:

USE master;
GO

SELECT  
    DB_NAME(database_id) AS DatabaseName,
    object_id,
    name AS ObjectName,
    type_desc AS ObjectType,
    create_date,
    modify_date
FROM 
    sys.system_objects
WHERE 
    database_id = 32767; -- Resource Database ID is always 32767
ORDER BY 
    name;

Example Explanation:

• This query selects details about all objects in the resource database (object ID, name, type, creation and modification dates) by filtering on database_id.

Step 7: Analyze Data in msdb Related to SQL Server Jobs

You can also explore how jobs are configured and their execution history through msdb. Let’s see a list of defined jobs:

USE msdb;
GO

SELECT name AS JobName,
    description AS JobDescription,
    enabled AS IsEnabled
FROM dbo.sysjobs;

Example Explanation:

• The query fetches job names, descriptions, and activation status (enabled field) from dbo.sysjobs in the msdb database.

Step 8: Check the File Paths for Each System Database

It's crucial to know the file paths of system databases, especially for recovery and maintenance purposes.

EXECUTE sp_helpfile;

Example Explanation:

• This stored procedure, when executed in the context of any database, will show the paths of all system databases on the SQL Server instance.

Summary

In this guide, we covered:

• Switching contexts between system databases.
• Viewing schema and tables in master.
• Checking file settings for model.
• Reviewing backup history stored in msdb.
• Inspecting temporary objects in tempdb.
• Examining system objects in the resource database.
• Looking at job information in msdb.
• Obtaining file path details for system databases via sp_helpfile.