概述

MSDB 數(shù)據(jù)庫(kù)是 4 個(gè)可見系統(tǒng)數(shù)據(jù)庫(kù)之一，另外3個(gè)分別是master、model 和 TempDB 。MSDB目的是跟蹤一系列常見 DBA 活動(dòng)歷史記錄，如備份和恢復(fù)。它還包含 SQL Server 代理的所有數(shù)據(jù)，包括作業(yè)、步驟、運(yùn)算符、警報(bào)和執(zhí)行歷史記錄。有時(shí) MSDB 用于存儲(chǔ) SSIS 包，盡管它更常見地存儲(chǔ)在實(shí)例上的 SSIS 目錄數(shù)據(jù)庫(kù)中。下面探討一下MSDB一些特性

MSDB特性

1、備份和恢復(fù)歷史存儲(chǔ)在 msdb 中

msdb 包含備份還原歷史記錄。因此，我們可以通過(guò)查詢相應(yīng)的表來(lái)獲得幾乎所有關(guān)于已執(zhí)行備份的信息。在 SSMS 中，我們可以在msdb > Tables > System Tables下找到這些表：

例如，backupset表包含有關(guān)每個(gè)備份集的信息。如果我們沒有對(duì)實(shí)例執(zhí)行任何備份，則此表將為空：

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

如我們所見，備份集表中沒有行：

現(xiàn)在，讓我們進(jìn)行備份，看看相關(guān)信息是如何存儲(chǔ)在表中的。為此，我們右鍵單擊 SSMS 上的用戶數(shù)據(jù)庫(kù)，選擇 任務(wù) > 備份...：

然后，我們選擇備份類型（我們選擇了完整備份）和備份文件的名稱：

備份完成后，我們?cè)俅芜\(yùn)行之前的查詢，可以看到backupset表現(xiàn)在返回了數(shù)據(jù)。它包含已執(zhí)行備份的記錄：

如果我們想要獲取在實(shí)例上執(zhí)行的備份的更詳細(xì)信息，可以運(yùn)行關(guān)于備份表的關(guān)聯(lián)查詢。例如，下面的查詢返回實(shí)例上成功完成的每個(gè)備份的數(shù)據(jù)庫(kù)名稱、備份文件名和路徑、備份類型、大小、開始和完成日期：

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 數(shù)據(jù)庫(kù)包含 SQL Server 代理作業(yè)相關(guān)信息

SQL Server 代理作業(yè)的信息也存儲(chǔ)在 msdb 數(shù)據(jù)庫(kù)中。例如，通過(guò)使用下面的查詢，我們可以檢索當(dāng)前實(shí)例上作業(yè)的一些詳細(xì)信息：

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 數(shù)據(jù)庫(kù)的sysjobs和sysjobhistory表來(lái)查看實(shí)例中的作業(yè)執(zhí)行歷史記錄。結(jié)果為空，這意味著還沒有作業(yè)歷史記錄：

3、 msdb 數(shù)據(jù)庫(kù)包含用于創(chuàng)建作業(yè)、步驟、計(jì)劃等存儲(chǔ)過(guò)程

上面，我們使用 SSMS 可視化界面創(chuàng)建了一個(gè)作業(yè)。也可以使用相應(yīng)的存儲(chǔ)過(guò)程通過(guò) T-SQL 代碼創(chuàng)建作業(yè)。這些過(guò)程存儲(chǔ)在 msdb 數(shù)據(jù)庫(kù)中。為了更好地理解我們正在談?wù)摰拇鎯?chǔ)過(guò)程，讓我們生成上面創(chuàng)建的作業(yè)的創(chuàng)建腳本。我們右鍵單擊作業(yè)名稱，選擇Script Job as > CREATE To > New Query Editor Window：

在新的查詢窗口中，我們可以看到用于創(chuàng)建作業(yè)的完整腳本。為了輕松找到并突出顯示腳本中使用的 msdb 數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程，我們只需在搜索框中 鍵入msdb ：

由此可見，msdb數(shù)據(jù)庫(kù)的sp_add_job和sp_add_jobstep 存儲(chǔ)過(guò)程是用來(lái)創(chuàng)建作業(yè)和作業(yè)步驟的。我們可以通過(guò)在 msdb 數(shù)據(jù)庫(kù)的Programmability > Stored Procedures > System Stored Procedures下，找到這些存儲(chǔ)過(guò)程的代碼， 然后右鍵單擊其中一個(gè)并選擇 Modify：

這些過(guò)程只是與作業(yè)相關(guān)的存儲(chǔ)過(guò)程的兩個(gè)示例。在 msdb 中還有其他處理作業(yè)的過(guò)程，例如刪除作業(yè)和作業(yè)步驟、獲取 SQL Server 代理作業(yè)相關(guān)信息等過(guò)程。

4、msdb 數(shù)據(jù)庫(kù)包含與維護(hù)計(jì)劃相關(guān)的信息，但不在“sysdbmaintplan_”表中

維護(hù)計(jì)劃相關(guān)信息存儲(chǔ)在 msdb 數(shù)據(jù)庫(kù)中。讓我們創(chuàng)建一個(gè)維護(hù)計(jì)劃并查看相關(guān)信息的存儲(chǔ)位置。在 SSMS 中，我們使用維護(hù)計(jì)劃向?qū)?chuàng)建一個(gè)新的維護(hù)計(jì)劃：

我們選擇一個(gè)備份數(shù)據(jù)庫(kù)（完整）任務(wù)作為示例，并為兩個(gè)數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)備份數(shù)據(jù)庫(kù)任務(wù)：

創(chuàng)建好維護(hù)計(jì)劃后，我們?cè)趍sdb中查找相應(yīng)的信息。當(dāng)我們展開msdb 數(shù)據(jù)庫(kù)下的“System Tables” 時(shí)，我們可以看到一些以“sysdbmaintplan_”開頭的表。這可能會(huì)令人困惑，因?yàn)橐环矫妫櫭剂x，這些表應(yīng)該包含維護(hù)計(jì)劃相關(guān)的數(shù)據(jù)，但另一方面，當(dāng)我們查詢這些表時(shí)，它們是空的：

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

即使我們執(zhí)行了維護(hù)計(jì)劃，我們也可以看到這些表中沒有相關(guān)信息：

這是因?yàn)閺?SQL Server 2005 開始，這些表中的數(shù)據(jù)沒有更改，它們的存在只是為了保留現(xiàn)有信息，以防從舊版本升級(jí)。根據(jù)微軟的說(shuō)法，上述表將在 SQL Server 的未來(lái)版本中進(jìn)行剔除。但是，可以在 SSIS 包相關(guān)表中找到有關(guān)維護(hù)計(jì)劃的信息。由于任何維護(hù)計(jì)劃都會(huì)創(chuàng)建一個(gè)由 SQL Server 代理作業(yè)運(yùn)行的 SSIS 包，如果我們查詢 msdb 數(shù)據(jù)庫(kù)中的sysssispackages和sysssispackagefolders 表，我們可以在那里找到有關(guān)我們的維護(hù)計(jì)劃的信息：

USE msdb
GO  
SELECT * FROM sysssispackages

SELECT * FROM sysssispackagefolders

在結(jié)果集中，上面創(chuàng)建的維護(hù)計(jì)劃相關(guān)信息以紅色突出顯示：

5、msdb 數(shù)據(jù)庫(kù)包含與日志傳送相關(guān)的信息

日志傳送相關(guān)信息和存儲(chǔ)過(guò)程也存儲(chǔ)在 msdb 數(shù)據(jù)庫(kù)中。如果我們使用“l(fā)og_shipping”關(guān)鍵字過(guò)濾msdb數(shù)據(jù)庫(kù)的系統(tǒng)表，我們可以看到日志傳送相關(guān)的表：

如果我們?cè)谙到y(tǒng)存儲(chǔ)過(guò)程中使用相同的關(guān)鍵字，我們可以找到配置和監(jiān)控日志傳送的存儲(chǔ)過(guò)程：

結(jié)論

綜上所述，在本文中，我們論述了 msdb 數(shù)據(jù)庫(kù)的一些重要特性。我們看到備份恢復(fù)相關(guān)信息以及 SQL Server 代理作業(yè)、維護(hù)計(jì)劃和日志傳送相關(guān)信息都存儲(chǔ)在 msdb 中。此外，在 msdb 庫(kù)中還有用于實(shí)現(xiàn)和監(jiān)控不同數(shù)據(jù)庫(kù)任務(wù)的存儲(chǔ)過(guò)程，例如創(chuàng)建 SQL Server 代理作業(yè)或配置日志傳送等。