任何工作過程中，大事小事無時(shí)不在，駕駛空間也不例外。對許多人來說，擁有最美好回憶的駕車地點(diǎn)確實(shí)令人惱火和煩惱。我們的客戶——中國領(lǐng)先的垂直市場軟件供應(yīng)商，為有限的公共和私人市場的客戶提供服務(wù)，我們的戰(zhàn)略合作伙伴，努力管理駕駛空間，導(dǎo)致速度緩慢，從而降低工作效率。 

擁有5年以上經(jīng)驗(yàn)的高級.NET開發(fā)人員高文豪先生加入了西貢科技根據(jù)言鼎科技的客戶服務(wù)模型為我們的客戶建立的離岸開發(fā)團(tuán)隊(duì)，成功地徹底解決了這種情況，并按照步驟進(jìn)行了具體說明下面，這些信息可能對您有用： 

 問題

我們最近收到來自監(jiān)控系統(tǒng)的警報(bào)，通知我們數(shù)據(jù)庫服務(wù)器運(yùn)行速度極慢……第一個(gè)警報(bào)到達(dá)時(shí)，SQL Server 所依賴的磁盤已接近滿，緊接著第二個(gè)警報(bào)出現(xiàn)。

 解決方案之旅

首先，我嘗試RD到服務(wù)器，驚奇地發(fā)現(xiàn)SQL Server安裝在D盤，總共只有10Gb左右。

確定服務(wù)器上的數(shù)據(jù)庫大小

我使用下面的 SQL 腳本列出了所有服務(wù)器數(shù)據(jù)庫的大小： 

與fs

AS (選擇database_id ,

           類型,

           大小* 8.0 / 1024 大小，

           文件編號

    從系統(tǒng)。主文件）

選擇 名稱，

       (

           選擇總和（大小）

           從fs

           其中 類型= 0

                 和fs 。數(shù)據(jù)庫 ID =數(shù)據(jù)庫。數(shù)據(jù)庫編號

       )數(shù)據(jù)文件大小MB ,

       (

           選擇總和（大小）

           從fs

           其中 類型= 1

                 和fs 。數(shù)據(jù)庫 ID =數(shù)據(jù)庫。數(shù)據(jù)庫編號

       )日志文件大小MB

從系統(tǒng)。數(shù)據(jù)庫數(shù)據(jù)庫

ORDER BY DataFileSizeMB DESC ;

正如我們所見，msdb 已占用 5 Gb，而磁盤僅占用 10 Gb。

msdb數(shù)據(jù)庫（SQL Server Agent Service）是用于SQL Server服務(wù)器各個(gè)組件的系統(tǒng)數(shù)據(jù)庫。除了 SQL Server 代理設(shè)置和任務(wù)信息之外，復(fù)制、日志傳送和維護(hù)計(jì)劃數(shù)據(jù)都保存在 msdb 數(shù)據(jù)庫中。

接下來，我們將確定數(shù)據(jù)庫中每個(gè)表的大小。

確定 msdb 中的表大小

我們可以使用下面的腳本來獲取 msdb 數(shù)據(jù)庫中每個(gè)表的大小。

使用[msdb] ;

去

選擇 頂部( 10 )

       --o.[object_id],

       obj = SCHEMA_NAME ( o . [schema_id] ) + '.' +哦。名字，

       哦。[類型] ,

       我。總行數(shù)，

       我。總大小

從系統(tǒng)。對象o

    加入

    (

        選擇我。[object_id] ,

               total_size = CAST ( SUM ( a . total_pages ) * 8. / 1024 AS DECIMAL ( 18 , 2 )),

               total_rows = SUM ( CASE  

                                       當(dāng)我。index_id IN ( 0 , 1 )

                                            和一個(gè)。[類型] = 1那么

                                           p . [行]

                                   結(jié)尾

                               )

        從系統(tǒng)。指數(shù)我

            加入系統(tǒng)。分區(qū)p

                在我。[object_id] = p 。[object_id]

                   和我。index_id = p 。索引號

            加入系統(tǒng)。allocation_units一

                上頁。[partition_id] =一個(gè)。容器編號

        我在哪里 is_disabled = 0

              和我。is_hypothetical = 0

         按i分組。[object_id]

    )我

        開o 。[object_id] =我。[object_id]

在哪里？[類型] IN ( 'V' , 'U' , 'S' )

由我訂購。total_size DESC ;

以上結(jié)果表明，大部分記錄都是備份歷史的一部分。

現(xiàn)在讓我們看看兩個(gè)表，它們是什么？他們現(xiàn)在是什么？

dbo.backupset為每個(gè)備份集包含一行。備份集包含來自單個(gè)成功備份操作的備份。

dbo.backupfile為數(shù)據(jù)庫的每個(gè)數(shù)據(jù)或日志文件包含一行。

此外，我們還可以通過閱讀幾個(gè)知識點(diǎn)站點(diǎn)來了解更多或查找有關(guān)其他表的更多信息。  

減少備份和恢復(fù)歷史表的大小

通過刪除備份條目，我們可以最小化這些表的大小。幸運(yùn)的是，微軟為我們提供了一個(gè)存儲過程來處理這些工作，我們不需要在每個(gè)數(shù)據(jù)庫中手動(dòng)刪除數(shù)據(jù)。

假設(shè)我們要?jiǎng)h除超過 3 個(gè)月的備份記錄，那么腳本是：

使用數(shù)據(jù)庫；  

去  

-- 刪除所有超過3個(gè)月的歷史日志訂單

聲明@oldest_date DATETIME = DATEADD ( MONTH , - 3 , GETDATE ())

EXEC sp_delete_backuphistory @oldest_date ;   

讓我們看看執(zhí)行腳本后的結(jié)果。

表的大小從 5Gb 顯著減少到不到 400Mb。好的！

盡管如此，這還不是結(jié)束！

我們知道，在每次備份或還原操作之后，備份和還原歷史表中都會添加更多的行，并且每天都會擴(kuò)展。因此，通常需要sp_delete_backuphistory 。

這次將實(shí)施維護(hù)計(jì)劃?，F(xiàn)在讓我們繼續(xù)配置歷史記錄的清理任務(wù)。 

SQL Server 維護(hù)計(jì)劃歷史清理任務(wù)

我們要清理歷史?？梢酝ㄟ^Wizard維護(hù)計(jì)劃和Designer維護(hù)計(jì)劃來設(shè)計(jì)。要設(shè)計(jì)它，讓我們使用向?qū)А?/span>

在 SSMS 中遵循以下過程以啟動(dòng)向?qū)В?/span>

轉(zhuǎn)到Management > Maintenance Plans并右鍵單擊，然后選擇Maintenance Plan Wizard：

點(diǎn)擊“下一步”

填寫任務(wù)的名稱和描述，然后單擊“更改... ”以設(shè)置計(jì)劃。

選擇您合適的時(shí)間表，然后單擊“確定”

仔細(xì)檢查計(jì)劃信息是否正確，然后單擊“下一步”

之后，我們顯然選擇“ Clean Up History ”任務(wù)，點(diǎn)擊“ Next ”：

在下一個(gè)窗口中，我們設(shè)置任務(wù)的配置。正如我們所看到的，我們可以選擇需要清理哪種歷史數(shù)據(jù)備份和還原歷史記錄、SQL Server 代理作業(yè)歷史記錄和維護(hù)計(jì)劃歷史記錄。此外，我們可以設(shè)置要保留的數(shù)據(jù)量：

我們將從msdb中清除所有提到的超過三個(gè)月的歷史數(shù)據(jù)記錄類型。然后，我們點(diǎn)擊“下一步”

最后，我們點(diǎn)擊完成創(chuàng)建計(jì)劃：

這樣，我們的清理任務(wù)就創(chuàng)建成功了：

如果我們刷新 Maintenance Plans 和 SQL Server Agent Jobs，我們可以找到新創(chuàng)建的計(jì)劃和對應(yīng)的作業(yè)。

從此以后，我們再也不用擔(dān)心產(chǎn)生不必要的信息而導(dǎo)致磁盤又滿了。