SQLSERVER 進階資料庫修復-結尾紀錄備份

資料庫損壞時，最佳的建議是透過備份檔還原，將資料庫回復到正常的狀態，當然，前提是備份檔是正常的。

不過，使用資料庫備份還原，最大的問題是可能會有一小段時間的資料遺失(RPO > 0)。

舉例來說，通常的備份策略是一天或一週做一次全備份，而在兩個全備份的期間，則定時做差異或交易紀錄備份。

因此，當資料庫損壞時，可能會有一小段時間是沒有備份的，這一小段時間的交易紀錄，稱為「結尾紀錄 tail-log」。

   

  

早期初學時，遇到像圖中這種狀況，因對DB不夠了解只能自認倒楣，然後用已經備份的檔案還原，並且通知使用者資料庫壞了，而且可能會有一小段時間的資料損失，接著就是一連串的檢討及補key資料。

後來為了避免這種狀況，就開始學習建HA，主要目的就是為了DB災害發生時不再有資料損失，並且降低DB停止服務的時間，也就是大家常講的RTO、RPO。

隨著對DB逐漸的熟悉，就想到如果在DB損壞的情況下還能把最後這段結尾紀錄備份出來，那麼就算沒有HA，光靠備份與還原也能做到「資料零損失」，只是回復的時間會比較長。

經過斷斷續續的測試及google大神的幫助，我發現，只要是交易紀錄檔(ldf)沒壞掉，即使是DB壞的無法online了，都有很大的可能將結尾紀錄備份出來，再配合之前的定期備份檔進行還原，達到「資料零損失」的目地。

因此，當資料庫損壞時，如果你的DB沒有HA，你應在第一時間，嘗試備份「結尾紀錄 tail-log」，以最大程度的減少資料的損失。

資料庫損壞，但仍在線上可用

第一種狀況是資料庫損壞，但DB仍然ONLINE中可以使用。

這時候，應先將DB設為單一使用者模式，並首先嘗試以一般指令備份交易紀錄，如果成功，那麼就可以使用之前的備份加上結尾紀錄備份，將資料庫還原至最近的時間點。

如果正常備份交易紀錄失敗，則可試著在指令後加上 with no_truncate，例如：

backup log MyDB to disk='c:\test\MyDB.trn' with noinit

go

/*

備份失敗

訊息 945，層級 14，狀態 2，行 1

檔案無法存取、記憶體或磁碟空間不足，因此無法開啟資料庫 'MyDB'。詳細資訊請參閱 SQL Server 錯誤記錄檔。

訊息 3013，層級 16，狀態 1，行 1

BACKUP LOG 正在異常結束。

*/

資料庫損壞，無法執行結尾紀錄備份，我們在backup log指令後，使用no_truncate

backup log MyDB to disk='c:\test\MyDB.trn' with noinit,no_truncate 

go --OK，備份成功

接著就可以使用restore指令，將資料庫還原至最後的時間點。

以下節錄微軟官網對於no_truncate的解釋

NO_TRUNCATE

指定不截斷記錄，並使 Database Engine 嘗試進行備份，而不論資料庫狀態為何。 因此，利用 NO_TRUNCATE 建立的備份可能會有不完整的中繼資料。 在資料庫已損毀的情況下，您可以利用這個選項來進行記錄的備份。

BACKUP LOG 的 NO_TRUNCATE 選項相當於同時指定 COPY_ONLY 和 CONTINUE_AFTER_ERROR。

未使用 NO_TRUNCATE 選項時，資料庫必須處於 ONLINE 狀態。 如果資料庫處於 SUSPENDED 狀態，您就能透過指定 NO_TRUNCATE 來建立備份。 但是，如果資料庫處於 OFFLINE 或 EMERGENCY 狀態，即使設定了 NO_TRUNCATE，也不允許 BACKUP。

資料庫損壞，且己不可用

第二種狀況是資料庫損壞，而且已經不可用。

例如DB損壞SQL SERVER自動將DB離線，或者DB被置於Suspect、OFF-LINE，或者用no_truncate也無法備份…等。這些情況下DB基本上已無法存取，也無法備份交易紀錄。

在這種情況下，有可能是mdf損壞，但ldf是好的。我們可以試著用「hack-attach」的方法，將結尾紀錄備份出來。

Hack-Attach

在另一台相同版本的Server

一、建立一個同名的資料庫（dummy DB），包含mdf及ldf的名稱都相同。

二、將dummy DB離線。ALTER DATABASE dummyDB SET OFFLINE;

三、刪除dummy DB的mdf及ldf等所有資料檔。

四、將損壞的DB的ldf檔，複製到dummy DB的ldf檔所在目錄，取代新建的ldf。

五、ALTER DATABASE dummyDB SET ONLINE; --會發生錯誤，忽略它

六、BACKUP LOG dummyDB TO DISK = '......'  WITH NO_TRUNCATE

經過上述步驟後，應可將結尾紀錄備份出來，就可以用之前的備份檔加上結尾紀錄備份，將資料庫還原至最近的時間點。

其實這個方法就是做一個假的資料庫，在OFF-LINE後將要做結尾紀錄備份LDF覆蓋掉新建的LDF，接著將資料庫ONLINE，最後將交易紀錄備份出來。這有點像是欺騙SQL SERVER，故稱為hack-attach.

如果你的DB沒有HA，而僅是靠著備份檔在做備援，那麼你更應該要熟悉或知道這些方法，當災害發生時儘量減少資料的損失。

SQLSERVER 交易紀錄碎片的解決方法與交易紀錄的收縮(shrink)

上篇討論了關於何謂交易紀碎片以及它對於的效能影響。

當你發現你的DB有交易紀錄碎片的問題時，首先要做的就是「降低碎片」，依照上篇所講的觀念，所謂的「降低碎片」，說白了就是「減少VLF的數量」。

VLF一旦被建立後，無法改變它的大小。所以「減少VLF的數量」意味著我們要刪除一些VLF。

而刪除VLF，主要是透過dbcc shrinkfile來達成，當然，如果你的DB容許暫停服務的話，你也可以直接把log砍掉重練，這裡我們不多做討論，本篇是使用dbcc shrinkfile。

要想更深入的理解dbcc shrinkfile，要先對VLF有些基本概念，如果你只想知道如何做，可以直接跳至「VLF碎片的解決」

VLF基本觀念

我們可以使用dbcc loginfo來查看VLF的資訊，以下圖為例，該資料庫的交易紀錄檔LDF，共被劃分為10個VLF來管理。

dbcc loginfo顯示的訊息中係依StartOffset排序，StartOffset代表了該VLF在LDF檔中的偏移量，也就是VLF的實體存放位址，也代表著VLF的順序。

FseqNo則代表了每個VLF的編號（0表示從未使用過）。此編號並不是固定的，它可以視為是一個會遞增的編號。

VLF是循環使用的，資料庫的交易紀錄從第一個FseqNo開始寫，當這個VLF寫滿後，即跳到下一個VLF寫入，並給予下一個VLF一個新的FseqNo編號（+1）。直到所有的VLF用完後，若之前的VLF已經可以重用，就會再循環使用。如果沒有VLF可用了，就會觸發自動成長。如果自動成長關閉或者因磁碟空間不足無法自動成長時，即會跳出交易紀錄檔已滿的錯誤訊息。

  

我們測試建立一個table如下

create table testVLF(c1 int identity,c2 char(1024))

接著新增約2000筆資料後，再執行dbcc loginfo

由上圖可以看到，目前交易紀錄的總量，佔用了3個VLF。

SQLSERVER對每筆交易紀錄，都會賦予一個唯一的編號（Log Sequence Number 簡稱LSN）。LSN 是經過排序的，因此如果 LSN2 大於 LSN1，表示 LSN2 所參考記錄中描述的變更，發生在記錄 LSN1 所描述的變更之後。

一、      在每個DB的交易紀錄中，LSN是唯一的。

二、      LSN是有順序性的，它是隨著交易的先後順序遞增，值越小的表示順序越前。

三、      LSN可以進行比較或排序，以方便建構還原順序。

我們用fn_dblog()去查看transaction Log時所到看的LSN，如下圖

圖中可以看到真實的LSN是一個「3段數值組成的字串」，3個數值中間以「:」隔開。LSN三段式的意義如下：

n  VLF序號（file sequential number）

n  VLF中的偏移量（offset）（以512bytes為單位度量）

n  Slot 編號

以上圖的最後一個LSN為例

000009b6:000016d9:0004 

在此篇中，我們有興趣的是第一部份「000009b6」，這是16進位的表示法，換算成10進位，即為「2486」。

各位可以再對照之前dbcc loginfo的結果圖，目前寫到的VLF，其FSeqNo的值即為2486。這表示了這筆交易紀錄是存在放FSeqNo=2486這個VLF之中。

（哈哈，這裡有點講過頭了，LSN的概念在一般的DBA工作上，可能永遠也用不到，但你如果對交易紀錄的結構有興趣，這則是必備的常識）

每個VLF視其中存放的交易紀錄的情況，在dbcc loginfo的結果中，基本上會有0跟2兩種狀態（status）

status 0：Reusable or Unused à代表可使用的空間

status 2：Active or Recoverable à代表不可使用的空間

它的意義在官網的說如下

Active (簡單的說就是：交易使用中，不能shrink)

The active portion of the log begins at the minimum LSN representing an active transaction. The active portion of the log ends at the last LSN written. Any VLFs that contain any part of the active log are considered active VLFs.

Recoverable （交易已結束，但因為有其它的：如mirror,replication，還沒完成，所以不能shrink）

The portion of the log preceding the oldest active transaction is needed only to maintain a sequence of log backups for restoring the database to a former state.

Reusable （原本有在用，但資料已經備走了或者不再需要，可以shrink）

If transaction log backups aren’t being maintained or you’ve already backed up the log, VLFs before the oldest active transaction aren’t needed and can be reused. Truncating or backing up the transaction log changes recoverable VLFs into reusable VLFs.

Unused（閒置中，從沒用過的，可以shrink）

One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place.

VLF 碎片的解決

要解決VLF的碎片，簡單的說，就是刪除過多的VLF，並且設定適當的自動成長。
底下列出要執行dbcc shrinkfile來解決VLF碎片的步驟：

1. 如果DB復原模式是full或bulk logged，請先執行backup log，目的是將交易紀錄檔的資料清空。

2. 如果DB復原模式是simple，執行checkpoint，目的同樣是為了將交易紀錄檔的資料清空，但也可以不用做，因為系統自己會做。

3. 使用dbcc loginfo觀察最大的active VLF（status<>0的max FSeqNo），最好位於log file的前面開始處。

4. 執行dbcc shrinkfile(‘邏輯檔名’,收縮目標大小)，收縮log file到一個適當的大小，至於何謂適當的大小，這完全看你的系統狀況而定了，當然最佳的選擇是收縮到MinimumSize後，再重新建立新的VLF。

5. 修改並設定適當的log file大小（alter database），請注意，設定時請參考上篇文章自行計算，一個VLF不要大於500MB。

6. 若有設定自動成長，應視實際環境，給予固定的大小，不要給太小，以免日後又造成VLF Fragemention。

VLF的數量及大小多少才適合，有幾個重要的參考指標：

一、每個VLF size不超過500MB。

二、VLF的數量上限不要超過1000個。

三、VLF數量的甜蜜點（sweet spot）為100個。

dbcc shrinkfile的概念

使用dbcc shrinkfile 的指令去縮小LDF檔時，其實非常的單純，就是以StartOffSet為順序，從尾部開始，將 status 為 0 的VLF收回，但在所有VLF中至少需有一個可用（status =0 ）的VLF。

SQL SERVER會試著去完成你所要求的收縮目標大小，當它發現VLF尾部的空間即使全放掉，也達不到你要求的收縮目標大小時，會自動在log中塞入一些NO-OP log，也就是一些無意義的log，這個目的是為了讓active VLF往下跳一個，以便backup log或checkpoint執行時能清除卡關的那個VLF。

如果你的DB的復原模式是simple mode，它會自動再做一次checkpoint，去試著清除LOG，這些動作的目地都只是為了讓shrink後的大小能符合你收縮目標大小的設定。

因此有時候明明資料沒多少但卻不能shrink，主要是目前的active VLF正好在LDF檔案的尾端。

以上圖這種情況，如果DB是full mode，執行交易紀錄的shrinkfile，將無法釋放任何的空間，因為最後一個VLF的狀態為2。

這時候你可以等待或手動產生一些交易行為，然後執行backup log(full mode)或checkpoing(simple mode)並觀察「最高的active」VLF，最佳的位置是在LOG File的開頭處。然後再執行shrink。

至於 AGs 的log shrink，由於shrink是寫入的動作，所以只能在主要節點進行shrink。

dbcc shrinkfile(‘邏輯檔名’,收縮目標大小)，如果目標大小給0的話，表示shrink 到 MinimumSize (初始化大小)。

mdf/ndf檔的 MinimumSize，可以從 dbcc page(xxx,1,0,3) 中的 MiniSize 看出來，但log file則無法使用dbcc page去看。

要查詢一個資料檔或交易紀錄檔的MinimumSize，其實可以簡單的借用 dbcc shrinkfile(‘邏輯檔名’, 99999) ，意思是給一個大值，確保它不會真的去壓縮，而只是想看它回傳的訊息。

圖中MinimumSize(8K Page)即是

所以，shrinkfile如果大小給0，它只會 shrink 到 MinimumSize，請注意。

資料行名稱	Description
DbId	Database Engine 試圖壓縮之檔案的資料庫識別碼。
FileId	Database Engine 試圖壓縮之檔案的檔案識別碼。
CurrentSize	檔案目前所佔的 8 KB 頁數。
MinimumSize	檔案所能佔用的 8 KB 頁數最小值。 這對應於檔案的大小下限或最初建立的大小。
UsedPages	檔案目前所用的 8 KB 頁數。
EstimatedPages	Database Engine 估計檔案可以壓縮成 8 KB 頁面的數目。

常常在shrinkfile時發現明明交易紀錄檔的「閒置空間」很大，但就是shrink不下來，後來經過一番研究才逐漸了解它背後的運作方式，此時才知道有個東西叫VLF，接著才又知道原來VLF也有碎片.，然後又再潛入交易紀錄檔的解析….等。
當逐漸了解交易紀錄檔的本質及運作原理後，在處理一些跟交易紀錄有關的問題時，心態就穩定許多，不容易再被一些問題的表象迷惑。