SQL q & a 的記錄檔：

發行項
08/18/2016

交易記錄是任何一個 SQL Server 執行個體不可或缺的重要元件。但是適當且有效的管理這些記錄檔同樣也很重要。

Paul s。 Randal

快速的成長率

**問：**我已成功地使用線上索引重建由於引進 SQL Server 2005 中。我已經也減少的交易記錄檔成長，重組索引時使用的大量記錄復原模式。我們最近升級為 SQL Server 2008年，交易記錄檔現在更甚以往成長。您可以解釋發生了什麼動作？

**答：**索引重建作業之前永遠 SQL Server 2005 的重建作業期間需要封鎖的鎖定。重新建置叢集的索引的目的 (沒有同時發生的讀取或寫入器) 的獨占資料表鎖定。重建非叢集索引是共用的資料表鎖定 (沒有同時撰寫者資料表)。

隨著「企業版的 SQL Server 2005 中的線上索引作業，鎖定已變更的線上作業。封鎖了只鎖定短期的開始和結尾的作業 (請參閱這個的部落格文章如需說明)。

在 SQL Server 2005 中，所有的索引重建作業會使用最小的記錄。這表示只有頁面配置登，而不是所有的資料列插入至新的索引。這會顯著減少索引重建作業所產生的交易記錄檔資料錄數量。這也表示交易記錄檔本身不一定要一樣大，(因為它不必容納完整記錄的索引重建作業)。使用大量記錄] 或 [簡單復原模式時，才會發生這個問題。所有的作業會完整記錄在完整復原模式，因此名稱。

SQL Server 2008 形式，從線上索引作業已變更為在所有的復原模式會完整記錄下來。如此可確保資料庫還原作業，包括其交易記錄檔中的線上索引作業就不會遇到任何問題。 (如需詳細的說明，請參閱 Microsoft 的知識庫文件 2407439。)

如果交易記錄檔成長的限制是比重建索引時允許並行的資料表存取更重要的不幸的是您要還原成離線的索引重建取回該最少記錄的行為。另一個應考慮的事項使用改變索引...重新組織後以移除片段。這永遠不會導致封鎖的運作方式，可以減少的交易記錄檔。 (有是重建並重新組織中的下列答案的其中一個比較)。

讓您的備份

**問：**我們一直想要設計備份策略，讓我們的完整備份中包含匯入程序中的所有資料，我們每晚的資料匯入程序與相合。我們尚未經過能夠找出可靠的方式，若要執行這項操作，有時資料是有，有時則不是部份是否有。您可以幫忙嗎？

**答：**您指明，「有時資料是所有，而有時不是部份有」。這使我認為您進行一次的大型交易為您匯入程序。

如果這是您正在做什麼，您可以考慮將它分割成較小的交易。這會讓 SQL 的清除交易記錄檔匯入交易之間更容易造成及較長期封鎖。長期執行的單一交易處理程序可能會導致鎖定擴大為獨占資料表鎖定。您不能變更匯入程序中，但您應該這麼如果可以的話。

完整備份有兩個階段：讀取資料，以及讀取交易記錄檔。當它已完整讀取的資料時，將會再標示的交易記錄檔從點上一步就是必要 (請參閱我 2009 年 7 月文件中，"了解 SQL Server 備份，「如需詳細資訊)。

當您還原完整備份時，指向 [資料庫還原即完成備份的資料讀取部分時的時間。交易記錄檔備份中包含的本質上執行還原的資料庫損毀修復。如此一來，很一致。

您必須復原任何交易帶到那個時候，就像一般的損毀修復。如果仍在資料讀取部分完成的情況下，它便會還原在還原操作時執行匯入程序。

若要保證特定交易的唯一方法是包含在完整備份是序列化作業，因此在該交易完成之前備份開始。否則，就無法知道是否在交易完成之前資料讀取已完成。

當然沒有序列化作業的替代方案。匯入程序完成後，請執行交易記錄檔備份。然後還原完整備份加上的交易記錄檔備份。它將會包含整個匯入程序交易。

若要重建或重新組織

**問：**我正在設計的索引維護策略。我不懂為什麼有兩種方法可以移除索引片段。我要如何判斷是否要重新建置我們的索引，或重新組織它們？我找不到所需的兩個方法之間的取捨的清單。

**答：**有兩種方法來移除片段，因為我寫了 SQL Server 2000 DBCC INDEXDEFRAG。這兩種方法是必要的因為它們有相當不同的特性。不幸的是，沒有空白紙張充分描述三種方法在 SQL Server 2005 中開始之間的差異：變更索引...重新組織 (新 DBCC INDEXDEFRAG)，更改索引...重建 (新的 DBCC DBREINDEX) 和線上版本的變更索引...重建。

以下是快速比較重建並重新組織變更索引選項：

重建需要卸除舊之前建立新的索引。這表示有足夠的可用空間在資料庫中，以容納新的索引。否則，資料庫會提供所需的可用空間會變。這可能會有問題的大型索引項目。重新組織只需要 8 KB 的額外空間在資料庫中。
重建可以使用多個 Cpu，所以作業的執行會加快。重新組織是永遠單一執行緒。
重建可能需要長期的鎖定，可以限制並行作業在資料表上。重新組織並不會發生封鎖的鎖定 (永遠是線上操作)。
重建可以使用來減少交易記錄檔成長的最小記錄。重新組織會永遠完全記錄，但不會防止交易記錄檔清除。
重建都會自動重建所有索引資料行統計資料，而重新組織根本不會更新統計資料。

因此，有數種折衷的鎖定，記錄和平行處理原則的磁碟空間需求。最重要的是，將會是這兩項操作的演算法差異。

索引重建永遠會重建整個索引，不論分散程度。這表示低負載分散索引的索引重建太誇張了。索引重新組織只會處理現有的分散程度。如此一來移除片段少量分散的索引，從較佳的選擇，但過於分散的索引中移除片段是不佳的選擇。在這種情況下會更好，剛剛重建索引。

這會導致我們選擇重建並重新組織之間的各種臨界值。有 sys.dm_db_index_physical_stats 的輸出中的 [avg_fragmentation_in_percent] 欄位為基礎的廣泛使用之片段臨界值：

0%到 10%：不執行任何動作
10%到 30%：使用變更索引...重新組織
30%或更：使用變更索引...重建

這些是一般的指導方針，您可能會發現不同的值更能在您的環境。您也必須考慮您是否要使用資料庫鏡像，必須完全復原模式。這表示索引重建作業將會完整記錄下來。許多人發現這會產生太多有效率地鏡像的主體與鏡像之間傳送的交易記錄檔。在這種情況下，它可以是最好重新組織索引減到最少的交易記錄檔項目。

您也可以考慮使用其他人的索引維護指令碼，以節省時間。 Ola Hallengren 有部份完整且廣泛使用的指令碼。

記錄檔大小的 Ups 和清單才

**問：**我只是變成 DBA。有一些新的資料庫開發人員的問題設定。我有問題，找出交易記錄檔應該是多少。我選擇時，任何大小會變大並維持這種方式。如果我將它縮小時，它仍會以相同大小一次。有方法，以建立資料庫時，正確設定的大小嗎？

**答：**首先，不要定期會壓縮交易記錄檔。當交易記錄檔需要成長時，新的空間配置給交易記錄檔必須是零初始化。這表示交易等待產生記錄檔資料錄可能必須等到此初始設定時。

如果您是在 shrink-grow，壓縮-成長週期的交易記錄檔中，您導致不必要的效能衝擊 SQL Server。最好是將交易記錄檔保留在其所需的大小。壓縮應該很少的作業，資料或交易記錄檔。

您可以估計的資料庫作業的交易記錄檔大小。閱讀更多有關的我 2011 年 1 月的專欄中。如果每個壓縮後的交易記錄檔成長穩定的大小，，可能是它應該是大小。除非它是真正以該大小，讓它太大。如果太大，判斷造成成長，以及是否分割成多個部分的作業，讓交易記錄檔已清除的機會。

Paul S. Randal

Paul s。 Randal是管理主管 SQLskills.com，Microsoft 地區 director 和 SQL Server 的 MVP。他在 1999 年 2007 年間服務於 Microsoft 的 SQL Server 儲存引擎 (SQL Server Storage Engine) 小組。他所撰寫的 SQL Server 2005 的 DBCC CHECKDB/修復，負責核心儲存引擎在 SQL Server 2008年開發期間。 Randal 專家嚴重損壞修復、高可用性和資料庫維護，而且在世界各地的會議。他的部落格在 SQLskills.com/blogs/paul，您可以在 Twitter 上發現他 Twitter.com/PaulRandal。

共用方式為