SQL 問答集大型交易記錄檔，何時使用修復，以及其他
Paul S. Randal
問：我注意到備份有一些怪異的行為，希望您能解釋一下。我們三不五時就會備份 62GB 的實際執行資料庫，重新整理應用程式開發人員所使用的資料。在還原新複本前，我們一定會先把舊的刪掉。還原的複本大小跟實際執行資料庫一樣，資料看起來也一樣，但是還原程序所花的時間卻遠比備份程序長。怎麼會這樣呢？為什麼跟備份比起來，還原會花這麼久的時間呢？
答：其實沒什麼好奇怪的，以您的情況來說，通常這都是預期之內的行為。備份與還原所需的時間會有差異，是因為每個程序必須執行的步驟不同。
備份資料庫包括兩個步驟，基本上它只是在資料庫上讀取 IO，然後在備份裝置上寫入 IO。
備份步驟 1 讀取資料檔中所有配置的資料，並將它寫入備份裝置。
備份步驟 2 讀取一些交易記錄檔，並將它寫入備份裝置。
實際所需的交易記錄量可能因情況不同而有很大的差異，但它其實是將還原的資料庫復原到一致時間點所需的數量 (針對這點，我在部落格文章有深入的解釋，網址是 sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx)。
另一方面，還原資料庫可能多達四個步驟，而且它所牽涉的工作，比單純讀取和寫入 IO 還要複雜得多：
還原步驟 1 假如資料庫檔案不存在，則建立它們。
還原步驟 2 從備份讀取所有的資料和交易記錄檔，並將它們寫入相關的資料庫檔案中。
還原步驟 3 在交易記錄檔上執行復原的 REDO 階段。
還原步驟 4 在交易記錄檔上執行復原的 UNDO 階段。
備份的兩個步驟所耗用的時間，大概跟還原步驟 2 所需的時間差不多 (假設硬體相似，而且伺服器沒有使用者活動的話)。如果資料檔很大，而且必須初始化為零 (這是 SQL Server 2000 的行為，以及 SQL Server 2005 的預設行為)，則還原步驟 1 可能會花很長一段時間。
若要避免這種情況，請不要在還原之前刪除現有的檔案。或者，您也可以選擇啟用立即初始化功能，快速建立檔案 (相關資訊，請參閱 msdn.microsoft.com/library/ms175935.aspx)。
還原步驟 3 和 4 是在還原的資料庫上執行復原，以便在交易層面保持一致性，這個程序就跟資料庫經歷故障修復的程序一樣。復原所花的時間，要根據所需處理的交易記錄量而定。比方說，如果在製作備份的同時，正在進行一項長時間執行的交易，那麼該交易的所有交易記錄檔都會備份起來，而且必須加以回復。
問：我不知道要選擇記錄傳送還是資料庫鏡像，來提供實際執行資料庫的備份複本。我顧慮的是必須在伺服器之間傳送的交易記錄量，特別是每晚進行的索引重建作業。聽說鏡像會傳送實際的重建命令，不是交易記錄檔，而重建作業是在鏡像上完成。是這樣嗎？如果是，那麼鏡像應該比用 BULK_LOGGED 復原模式進行記錄傳送更理想對吧？
答：您聽說的並非屬實。資料庫鏡像的運作方式，是將實際的交易記錄檔記錄從主體資料庫傳送到鏡像伺服器，在該伺服器的鏡像資料庫中「重新執行」。鏡像資料庫上不會進行任何形式的轉換或篩選，也不會進行任何一種 T-SQL 命令攔截。
資料庫鏡像只支援 FULL 復原模式，也就是說，索引重建作業一定會完整記錄下來。根據您所考慮的索引大小而定，它可能會產生大量的交易記錄檔，接著在主體資料庫上產生一個大型記錄檔以及可觀的網路頻寬，以便將記錄檔記錄傳送到鏡像。
您可以把資料庫鏡像想成即時記錄傳送 (事實上，這項功能在 SQL Server 2005 開發早期便是採用這個名稱)。在記錄傳送中，主要資料庫的交易記錄檔備份會定期傳送到次要伺服器，並在次要資料庫上還原。
記錄傳送支援 FULL 和 BULK_LOGGED 復原模式。在 FULL 復原模式的記錄傳送資料庫中進行的索引重建作業，它所產生的交易記錄量，跟鏡像資料庫所產生的量一樣。不過，在記錄傳送的資料庫案例中，資料是放在記錄備份 (或一連串記錄備份) 一起傳送到備份資料庫，而不是以連續流程傳送。
如果索引重建在完成時，記錄傳送資料庫所用的是 BULK_LOGGED 復原模式，那麼它所產生的交易記錄量最少。但是下一個交易記錄檔備份，也會包含由記錄最少的索引重建作業所變更的所有資料檔案範圍。這表示在 BULK_LOGGED 復原模式涵蓋索引重建的記錄檔備份大小，跟那些在 FULL 復原模式中涵蓋索引重建的記錄檔備份大小幾乎一模一樣。
因此需要傳送給備份資料庫的資料量，與在鏡像資料庫上和記錄傳送資料庫上的索引重建，幾乎完全一樣。實際的差別在於傳送資訊的方式 — 連續傳送或是批次傳送。
在這兩種方法之間擇其一時，必須考慮其他許多因素 (因素之多，無法在單期的《SQL 問答集》中一一討論)。在做決定之前，最好能夠看看這些因素符不符合您的需求 (例如，可接受的資料損失限制以及可允許的停機時間)。
問：我用的是 SQL Server 2005，其中一個資料庫的交易記錄檔不斷增大。這個資料庫是採用完整復原模式，而且我進行的是交易記錄檔備份。我記得這樣應該可以防止交易記錄檔變大吧。所以到底是哪裡出錯了呢？
答：在完整復原模式下製作交易記錄檔備份，的確可以防止交易記錄檔變大。但是導致交易記錄檔變大的可能因素不只於此。關鍵在於，到底是什麼東西需要用到 (或處於作用狀態) 交易記錄檔。除了缺乏交易記錄檔之外，其他像複寫、資料庫鏡像，以及作用中的交易等，都是可能造成問題的常見因素。
複寫是指以非同步的方式讀取您的交易記錄檔記錄，然後載入交易以複寫到另一個散發資料庫。只要是尚未被複寫記錄讀取器工作讀取的交易記錄檔記錄，都不能釋放出來。如果您的工作負載會產生許多交易記錄檔記錄，而且您設定執行複寫記錄讀取器的間隔時間也比較長，那就可能累積大量的記錄，而使交易記錄檔增大。
如果您是執行非同步資料庫鏡像，那麼也許是因為有積存的交易記錄檔記錄未從主體傳送至鏡像資料庫 (稱為鏡像 SEND 佇列的資料庫) 的緣故。交易記錄檔記錄要等到全都順利送出之後才能釋放。若是交易記錄檔記錄產生的頻率偏高，加上網路上的頻寬有限 (或其他硬體問題)，積存的規模可能會變得非常可觀，而導致交易記錄檔增大。
最後，如果使用者開始進行明確的交易 (例如使用 BEGIN TRAN 陳述式)，然後進行某些修改 (例如 DDL 陳述式，或插入/更新/刪除動作)，那麼所產生的交易記錄檔記錄必須一直保留到使用者認可或回復交易為止。這表示其他交易所產生的任何後續交易記錄檔記錄也不能釋放，因為交易記錄檔不能選擇性的釋放。比方說，如果使用者沒有完成當天的交易就回家了，該交易記錄檔就會繼續增長，因為交易記錄檔記錄不斷產生，卻無法釋放出來。
您可以查詢 sys.databases 系統目錄檢視，然後分析 log_reuse_wait_desc 一欄，藉此找出交易記錄檔無法釋放的原因，如下所示：
SELECT name AS [Database],
log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;
如果導因是源自於作用中的交易，您可以使用 DBCC OPENTRAN 陳述式取得與該交易相關的詳細資訊：
DBCC OPENTRAN ('dbname')
問：我聽說要從損毀復原，應該採用從備份還原的作法，最後不得已才使用 REPAIR_ALLOW_DATA_LOSS。您可以解釋為什麼不應該採用「SQL Server 2005 的修復」呢？如果它這麼「危險」的話，為什麼還把它放在產品中呢？
答：首先，編寫 SQL Server 2005 修復的正是敝人在下我。REPAIR_ALLOW_DATA_LOSS (以下稱為修復) 的問題出在它的運作方式模糊不清。修復選項的名稱由來，是因為執行它可能會導致資料庫資料損失。功能修復損毀資料庫結構一般採用的方式，是刪除損毀的結構，然後修正資料庫中參考或被已刪除結構參考的其他一切項目。修復其實是保持資料庫結構一致的最後一種途徑 — 它的重點並不是要儲存使用者資料。修復並不會大費周章去刪除使用者的資料，但也不會費盡心思去儲存使用者的資料。
這樣看來，修復好像不是一個很負責任的作法，但是當需要用到修復選項時，最快也最可靠的方法還是修正損毀。在嚴重損壞修復的情況下，速度是相當重要的因素，而正確性也不容許有任何差池。要設計出比它更複雜的修復演算法，並證明可在所有情況下快速且正確地運作，幾乎是不可能的。修復程式碼中有些複雜的演算法，會解決像是兩個索引配置有相同頁面或範圍的情況，但演算法主要是要修復加修正。
儘管如此，修復還是有些您應該注意的問題：
- 修復在刪除損毀結構時，並不會考慮外部索引鍵條件約束，因此它可能會從資料表刪除與其他資料表具有外部索引鍵關聯的記錄。在執行修復後一定要執行 DBCC CHECKCONSTRAINTS，才能判斷是否有發生這種情況。
- 修復並不會 (而且也無法) 考慮在應用程式層級定義的任何繼承商務邏輯或資料關聯，因為這些關聯可能會被一些已刪除的資料打斷。再次強調，您一定要執行應用程式內建的任何自訂一致性檢查，才能判斷有沒有任何東西已被破壞。
- 部分修復作業無法複製。如果在對等式拓樸中的發行者或節點上執行修復，可能會在拓樸當中產生不一致的情況，因此您必須手動進行修正。
因此，要從損毀復原時，選擇備份一定比選擇執行修復選項來得明智。但是在產品中提供修復，是因為您隨時有可能碰到資料庫損毀，卻沒有備份可用，而需要用某種方法讓資料庫快速上線的情況。
問：我才剛剛在新公司擔任 DBA 一職，負責數個應用程式及其後端資料庫。其中一個應用程式在進行更新時，效能非常低落。經過調查後，我發現該應用程式所使用的每個資料表上面，都有大量的索引。問過許多人之後，才知道原來前任 DBA 習慣在每個資料表資料行新增索引，外加一些組合。我不相信這些索引都有存在的必要 — 但我要怎麼判別哪些索引可以放心捨棄呢？我們執行的是 SQL Server 2005。
答：如您所臆測，數量龐大的索引很有可能是導致效能不良的主要肇因。每次在資料表中插入、更新或刪除資料列時，也必須在每個非叢集索引內進行相對應的作業。這在 I/O、CPU 使用率和產生交易記錄檔方面加重許多負擔。
要在 SQL Server 2000 中判斷用了哪些索引，唯一的辦法是使用設定功能，並分析查詢計畫。SQL Server 2005 有一個全新的動態管理檢視 (DMV) 會追蹤索引使用量 — sys.dm_db_index_usage_stats。
資料庫部分啟動之後，每次一使用索引，這個 DMV 都會加以追蹤 (包括追蹤使用方式)。當 SQL Server 關閉時，所有資料庫的統計資料都會跟著遺失，而當單一資料庫關閉或卸除時，其統計資料也會遺失。其理論是，如果索引沒有顯示在輸出中，就表示它從資料庫啟動以來尚未被用過。
要追蹤索引在一段時間內的使用量，有一個簡單的方法，就是定期擷取 DMV 輸出的快照集，然後比較這些快照集。許多人都忽略了一件事，那就是您必須追蹤整個商務週期的索引使用量。如果您只取得單天的快照集，可能會發現有好幾個索引都沒用到。但如果這些索引的目的是加速執行月底報告，也許就不應該移除它們。如果索引在整個商務週期都沒有用到過，那麼不妨直接放棄，取回空間，恢復效能。
如需一些可用來定期擷取 DMV 快照集的簡單程式碼，請參閱我的部落格文章，網址是 sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx。
Paul S. Randal 是 SQLskills.com 的常務董事，同時也是 SQL Server MVP。Paul 曾為 SQL Server 2005 撰寫過 DBCC CHECKDB/repair，並且在 SQL Server 2008 開發期間負責核心儲存引擎。身為嚴重損壞修復、高可用性和資料庫維護方面的專家，Paul 也經常在研討會上發表演說。他的部落格位於 SQLskills.com/blogs/paul。