系统库-SQL Server MSDB探究

概述

MSDB 数据库是 4 个可见系统数据库之一，另外3个分别是master、model 和 TempDB 。MSDB目的是跟踪一系列常见 DBA 活动历史记录，如备份和恢复。它还包含 SQL Server 代理的所有数据，包括作业、步骤、运算符、警报和执行历史记录。有时 MSDB 用于存储 SSIS 包，尽管它更常见地存储在实例上的 SSIS 目录数据库中。下面探讨一下MSDB一些特性

MSDB特性

1、备份和恢复历史存储在 msdb 中

msdb 包含备份还原历史记录。因此，我们可以通过查询相应的表来获得几乎所有关于已执行备份的信息。在 SSMS 中，我们可以在msdb > Tables > System Tables下找到这些表：

例如，backupset表包含有关每个备份集的信息。如果我们没有对实例执行任何备份，则此表将为空：

SELECT *  FROM [msdb].[dbo].[backupset]

如我们所见，备份集表中没有行：

 

现在，让我们进行备份，看看相关信息是如何存储在表中的。为此，我们右键单击 SSMS 上的用户数据库，选择 任务 > 备份...：

然后，我们选择备份类型（我们选择了完整备份）和备份文件的名称：

备份完成后，我们再次运行之前的查询，可以看到backupset表现在返回了数据。它包含已执行备份的记录：

如果我们想要获取在实例上执行的备份的更详细信息，可以运行关于备份表的关联查询。例如，下面的查询返回实例上成功完成的每个备份的数据库名称、备份文件名和路径、备份类型、大小、开始和完成日期：

USE msdb
GO
 
SELECT bs.database_name, bmf.physical_device_name, 
       CASE bs.type WHEN 'D' THEN 'FULL'
                    WHEN 'I' THEN 'DIFFERENTIAL'
                    WHEN 'L' THEN 'TRNSACTION LOG'
                    ELSE bs.type
            END AS BackupType,
       bs.backup_size AS BackupSizeInBytes,
       bs.backup_start_date, backup_finish_date
FROM   
backupmediafamily bmf
INNER JOIN
msdb.dbo.backupset bs
ON bmf.media_set_id = bs.media_set_id  

示列如下：

 

2、msdb 数据库包含 SQL Server 代理作业相关信息

SQL Server 代理作业的信息也存储在 msdb 数据库中。例如，通过使用下面的查询，我们可以检索当前实例上作业的一些详细信息：

USE msdb
GO
 
SELECT j.name,
       jh.run_date,
       jh.step_name,
       jh.run_time,
       jh.run_duration
FROM 
sysjobs j
INNER JOIN 
sysjobhistory jh
ON j.job_id = jh.job_id 

我们使用msdb 数据库的sysjobs和sysjobhistory表来查看实例中的作业执行历史记录。结果为空，这意味着还没有作业历史记录：

 

3、 msdb 数据库包含用于创建作业、步骤、计划等存储过程

上面，我们使用 SSMS 可视化界面创建了一个作业。也可以使用相应的存储过程通过 T-SQL 代码创建作业。这些过程存储在 msdb 数据库中。为了更好地理解我们正在谈论的存储过程，让我们生成上面创建的作业的创建脚本。我们右键单击作业名称，选择Script Job as > CREATE To > New Query Editor Window：

在新的查询窗口中，我们可以看到用于创建作业的完整脚本。为了轻松找到并突出显示脚本中使用的 msdb 数据库的存储过程，我们只需在搜索框中 键入msdb ：

由此可见，msdb数据库的sp_add_job和sp_add_jobstep 存储过程是用来创建作业和作业步骤的。我们可以通过在 msdb 数据库的Programmability > Stored Procedures > System Stored Procedures下，找到这些存储过程的代码， 然后右键单击其中一个并选择 Modify：

这些过程只是与作业相关的存储过程的两个示例。在 msdb 中还有其他处理作业的过程，例如删除作业和作业步骤、获取 SQL Server 代理作业相关信息等过程。

4、msdb 数据库包含与维护计划相关的信息，但不在“sysdbmaintplan_”表中

维护计划相关信息存储在 msdb 数据库中。让我们创建一个维护计划并查看相关信息的存储位置。在 SSMS 中，我们使用维护计划向导创建一个新的维护计划：

我们选择一个备份数据库（完整）任务作为示例，并为两个数据库创建一个备份数据库任务：

创建好维护计划后，我们在msdb中查找相应的信息。当我们展开msdb 数据库下的“System Tables” 时，我们可以看到一些以“sysdbmaintplan_”开头的表。这可能会令人困惑，因为一方面，顾名思义，这些表应该包含维护计划相关的数据，但另一方面，当我们查询这些表时，它们是空的：

USE msdb
GO
 
SELECT * FROM sysdbmaintplans
 
SELECT * FROM sysdbmaintplan_databases
 
SELECT * FROM sysdbmaintplan_jobs
 
SELECT * FROM sysdbmaintplan_history 

即使我们执行了维护计划，我们也可以看到这些表中没有相关信息：

这是因为从 SQL Server 2005 开始，这些表中的数据没有更改，它们的存在只是为了保留现有信息，以防从旧版本升级。根据微软的说法，上述表将在 SQL Server 的未来版本中进行剔除。但是，可以在 SSIS 包相关表中找到有关维护计划的信息。由于任何维护计划都会创建一个由 SQL Server 代理作业运行的 SSIS 包，如果我们查询 msdb 数据库中的sysssispackages和sysssispackagefolders 表，我们可以在那里找到有关我们的维护计划的信息：

 USE msdb
GO 
 
SELECT * FROM sysssispackages
 
SELECT * FROM sysssispackagefolders 

在结果集中，上面创建的维护计划相关信息以红色突出显示：

 

5、msdb 数据库包含与日志传送相关的信息

日志传送相关信息和存储过程也存储在 msdb 数据库中。如果我们使用“log_shipping”关键字过滤msdb数据库的系统表，我们可以看到日志传送相关的表：

如果我们在系统存储过程中使用相同的关键字，我们可以找到配置和监控日志传送的存储过程：

结论

综上所述，在本文中，我们论述了 msdb 数据库的一些重要特性。我们看到备份恢复相关信息以及 SQL Server 代理作业、维护计划和日志传送相关信息都存储在 msdb 中。此外，在 msdb 库中还有用于实现和监控不同数据库任务的存储过程，例如创建 SQL Server 代理作业或配置日志传送等。