SQL Monitor是一個SQL Server監(jiān)控工具。它可以監(jiān)控SQL Servers的健康狀況和活動，并通過電子郵件為您發(fā)送監(jiān)測結(jié)果和建議。

點擊下載SQL Monitor免費版

SQL Monitor不僅自動收集您需要的所有磁盤和數(shù)據(jù)庫增長跟蹤數(shù)據(jù)，而且還分析這些數(shù)據(jù)的趨勢以準(zhǔn)確預(yù)測何時磁盤卷會耗盡可用空間，或數(shù)據(jù)庫文件何時需要增長。本教程一共分為三個部分，這是最后一部分內(nèi)容——監(jiān)視數(shù)據(jù)庫文件增長。（上篇查看點擊這里，中篇查看點擊這里）

監(jiān)視數(shù)據(jù)庫文件增長

如果正確完成了初始數(shù)據(jù)和日志大小調(diào)整，并且如上所述，您正在仔細(xì)監(jiān)視文件的使用情況，那么使用文件自動增長功能，可以預(yù)期物理文件大小會增加，而不是臨時增加。

但是，眾所周知，為新數(shù)據(jù)庫預(yù)測數(shù)據(jù)存儲要求非常困難，甚至由于各種原因，即使是已建立的數(shù)據(jù)庫有時也可能會意外地自動增長。意外的數(shù)據(jù)導(dǎo)入，導(dǎo)致事務(wù)無休止地打開（防止日志截斷）的軟件錯誤，維護(hù)操作等等，都可能導(dǎo)致意外的快速增長。當(dāng)數(shù)據(jù)庫爆炸性增長時，它將導(dǎo)致文件自動增長事件，這可能會消耗大量CPU資源。如果發(fā)生在繁忙時段，則可能導(dǎo)致應(yīng)用程序進(jìn)程阻塞和中斷。

依靠自動增長不是一個好的文件大小管理策略。應(yīng)該啟用它，但僅將其用于提供“安全網(wǎng)”，以適應(yīng)文件突然增長和意外增長的情況（假設(shè)它們還確保容納數(shù)據(jù)和日志文件的磁盤卷具有足夠的空間來容納意外增長）。

換句話說，發(fā)生了自動增長，您需要了解它并調(diào)查原因。

臨時跟蹤數(shù)據(jù)庫增長

跟蹤數(shù)據(jù)庫增長的一種經(jīng)典方法是使用msdb.dbo.backupset，從每個數(shù)據(jù)庫的夜間完整備份中捕獲數(shù)據(jù)庫增長，并在Excel中隨時間跟蹤它。另外，sp_databases系統(tǒng)存儲過程將為您提供實例上所有數(shù)據(jù)庫的總大小（以KB為單位），或者您可以使用各種系統(tǒng)表和視圖。

目錄視圖的size列sys.database_files以8 KB頁的形式提供每個數(shù)據(jù)庫文件的大小，因此以GB為單位計算數(shù)據(jù)庫的總大小很簡單：

SELECT SUM(size) * 8.0 / 1024.0 / 1024.0 AS SizeInGB
FROM sys.database_files;

您可以通過查詢默認(rèn)跟蹤收集的數(shù)據(jù)來找出哪個數(shù)據(jù)庫最近經(jīng)歷了自動增長（或自動收縮）事件。但是，默認(rèn)跟蹤文件確實會滾動，因此歷史數(shù)據(jù)將被覆蓋，并且自上次滾動以來，您只會看到最近的自動增長事件。

在SQL Monitor中監(jiān)視數(shù)據(jù)庫增長

該總數(shù)據(jù)庫文件大小在SQL監(jiān)視器自訂指標(biāo)收集和分析數(shù)據(jù)sys.database_files（使用以前的查詢），隨著時間的推移。如果此度量標(biāo)準(zhǔn)檢測到整個數(shù)據(jù)庫大小發(fā)生了變化，則數(shù)據(jù)庫可能正在以意外的速度增長，因此DBA需要了解原因。數(shù)據(jù)庫增長是由數(shù)據(jù)增長還是日志文件增長引起的？

SQL Monitor提供了數(shù)據(jù)庫自動增長自定義指標(biāo)，該指標(biāo)將按計劃從默認(rèn)跟蹤中收集數(shù)據(jù)。如果指標(biāo)數(shù)據(jù)的收集周期比跟蹤文件滾動的周期短，那么您將不會錯過任何增長事件，并且可以為此指標(biāo)設(shè)置警報，因此您會立即意識到發(fā)生了異常的自動增長事件。

監(jiān)控數(shù)據(jù)文件的增長

當(dāng)數(shù)據(jù)文件增長時，它們可以使用即時文件初始化，從而允許SQL Server分配更多的磁盤空間，而無需在可以將任何數(shù)據(jù)寫入到其中之前對所有這些空間進(jìn)行零初始化。這使得每個增長事件都相對高效，但是如果文件需要頻繁擴(kuò)展，它仍然會導(dǎo)致阻塞問題，因為其他數(shù)據(jù)庫處理必須暫停，直到增長事件完成為止。隨著時間的流逝，它也可能導(dǎo)致物理文件碎片化。

前面描述的數(shù)據(jù)庫文件使用率和警報為您提供了監(jiān)視數(shù)據(jù)文件增長，以及文件內(nèi)空間使用所需的診斷數(shù)據(jù)。但是，您也可以使用數(shù)據(jù)庫文件大小自定義指標(biāo)（該指標(biāo)從sys.dm_os_performance_counters動態(tài)管理視圖（DMV）收集值）來跟蹤數(shù)據(jù)文件大小的變化。

監(jiān)視日志文件增長

SQL Server寫日志是為了添加、刪除或修改數(shù)據(jù)的每個事務(wù)，以及響應(yīng)數(shù)據(jù)庫維護(hù)操作（例如索引重建或重組，統(tǒng)計信息更新等），將日志寫入日志。即使是最勤奮的DBA有時也可能會因數(shù)據(jù)庫日志文件意外地快速增長而陷入困境，因此我們將更詳細(xì)地考慮日志增長。

與數(shù)據(jù)文件相比，事務(wù)日志文件無法利用即時文件初始化的優(yōu)勢，因此，每個日志增長事件在時間和資源上都相對昂貴。發(fā)生這種情況時，其他任何事務(wù)都將無法使用事務(wù)日志，并且數(shù)據(jù)庫將是“只讀的”，直到增長事件完成為止。

快速的日志增長可能是由于大規(guī)模數(shù)據(jù)或數(shù)據(jù)庫修改（例如，由于索引重建，長時間運行的數(shù)據(jù)清除或歸檔過程）或未提交的事務(wù)（防止日志中的空間重用）而導(dǎo)致的。

圖8顯示了SQL Monitor中的分析圖。我在圖表上僅繪制了兩個指標(biāo)，一個測試數(shù)據(jù)庫（MyTestDB）的日志文件總大小，以及該實例的機(jī)器處理器時間。它顯示了一段爆炸性的事務(wù)日志增長時期。

圖8

圖9顯示了爆炸性日志增長期間的服務(wù)器指標(biāo)圖，以及該期間運行的最昂貴的查詢。

圖9

在這種情況下，（人為）原因是對包含幾百萬行的Persons表的一系列更新。但是，在歸檔數(shù)據(jù)時，大規(guī)模數(shù)據(jù)清除可能會產(chǎn)生類似的影響。SQL Server必須將更改記錄到數(shù)據(jù)的每一行，并且由于存在約束而加劇了這種情況，并且觸發(fā)器加劇了問題。

例如，如果您要執(zhí)行數(shù)據(jù)清除，并且表通過FOREIGN KEY設(shè)計為的約束來引用目標(biāo)表CASCADE ON DELETE，則SQL Server還將記錄通過級聯(lián)約束刪除的行的詳細(xì)信息。如果表上有DELETE觸發(fā)器，則為了審核數(shù)據(jù)更改，SQL Server還將記錄觸發(fā)器執(zhí)行期間執(zhí)行的操作。所有這些都會導(dǎo)致爆炸性的原木增長，有時甚至?xí)茐淖钚燎诘脑鲩L預(yù)測計算。

在大規(guī)模數(shù)據(jù)更改期間控制日志增長

良好的做法是小批量運行大規(guī)模數(shù)據(jù)操作，如果在下一個CHEKPOINT運行（如果數(shù)據(jù)庫使用SIMPLE恢復(fù)模型），或者在下一個日志備份（如果運行），則可以在事務(wù)日志之間進(jìn)行截斷。 數(shù)據(jù)庫使用FULL或BULK_LOGGED恢復(fù)模型。

在本示例中，MyTestDB數(shù)據(jù)庫在FULL恢復(fù)模型下運行，因此僅在發(fā)生日志備份之后，才能截斷事務(wù)日志并重用現(xiàn)有空間。在這種情況下，數(shù)據(jù)庫的大小增長到超過20 GB（從最初的1 GB以下），完全是由于日志文件的增長（先前的數(shù)據(jù)文件使用情況警報未觸發(fā)，因為數(shù)據(jù)文件中未使用空間）。

SQL Monitor提供了一個自定義的“大事務(wù)日志文件”度量標(biāo)準(zhǔn)，可在具有大日志（超過10 GB，但可配置）的數(shù)據(jù)庫數(shù)量增加時發(fā)出警報。

如果由于某種原因阻止了日志空間的重用，則需要查詢sys.databases中的log_reuse_wait_desc列，以找出原因。 這通常是由于事務(wù)運行時間長或缺少日志備份（SQL Monitor也應(yīng)該提醒您！），但是還有其他可能的原因。

與不受控制的日志增長相關(guān)的問題（尤其是當(dāng)日志文件配置為以小增量增長時）會導(dǎo)致日志碎片，其中日志在內(nèi)部由大量的虛擬日志文件（VLF）組成。在上面的示例中，日志通過反復(fù)快速地增長，最終獲得了超過2000個VLFS，如運行所示DBCC LOGINFO。“內(nèi)部碎片化”日志可能會降低讀取日志的操作的性能，例如日志備份、復(fù)制和鏡像過程。它還可能減慢崩潰恢復(fù)的速度，因為SQL Server必須在開始恢復(fù)數(shù)據(jù)庫之前打開日志并讀取每個VLF。

設(shè)置FILEGROWTH日志的值時，您都希望避免因允許日志以小增量增長而導(dǎo)致的碎片化。但是不要過度使用它，因為如果將增量設(shè)置得太高，則可能會在正常的事務(wù)日志增長事件期間發(fā)生超時（由于需要對所有空間進(jìn)行零初始化）。通常建議使用固定的512MB自動增長大小作為指導(dǎo)，但是最好了解您自己系統(tǒng)上的日志增長特征和行為并相應(yīng)地設(shè)置自動增長大小。

總結(jié)

您可以手動設(shè)置警報，以在磁盤空間不足時發(fā)出警告，并希望及時提供更多空間以避免任何數(shù)據(jù)庫“中斷”。但是，這種反應(yīng)性磁盤空間管理僅能帶您到達(dá)目的地，因為它無法讓您知道這種情況將在多久之前發(fā)生。

勤奮的DBA希望通過了解數(shù)據(jù)庫文件中空間的使用速度來避免磁盤用盡的機(jī)會。諸如SQL Monitor之類的工具可以非常輕松地捕獲這些數(shù)據(jù)，但是它還可以做很多事情。它分析收集到的數(shù)據(jù)以捕獲數(shù)據(jù)增長趨勢，并使用它們來準(zhǔn)確預(yù)測何時您將用完空間。這使您有機(jī)會進(jìn)行計劃，而不僅僅是作出反應(yīng)，并最大程度地減少由于文件填滿或觸發(fā)文件自動增長而造成的任何干擾。

監(jiān)視磁盤和數(shù)據(jù)庫增長數(shù)據(jù)時，隨著時間的推移，您將更好地了解數(shù)據(jù)庫的增長特性，并提高磁盤容量調(diào)整和計劃的準(zhǔn)確性。這樣，文件自動增長事件將是一個例外，而不是正常情況，因此，您可以跟蹤自動增長事件，并在事件發(fā)生時得到警告，并調(diào)查導(dǎo)致意外增長的原因。

本教程內(nèi)容到這里就完結(jié)了，希望對您有所幫助~您可以點擊下方鏈接查看該教程前面兩部分內(nèi)容，或者下載SQL Monitor試用版體驗一下~

相關(guān)內(nèi)容推薦：

使用SQL Monitor避免耗盡磁盤空間（上）：監(jiān)視磁盤上的可用空間

使用SQL Monitor避免耗盡磁盤空間（中）：監(jiān)視數(shù)據(jù)庫文件中的可用空間

想要購買SQL Monitor正版授權(quán)，或了解更多產(chǎn)品信息請點擊