SQL SERVER的交易記錄

標籤：epg   ges   pip   ttf   運行時   cifs   usb   sbo   tac   

1 基本介紹

    每個資料庫都具有交易記錄，用於記錄所有事物以及每個事物對資料庫所作的操作。

    日誌的記錄形式需要根據資料庫的復原模式來確定，資料庫復原模式有三種：

• 完整模式，完全記錄事物日誌，需要定期進行記錄備份。
• 大容量記錄模式，適用於大量操作的資料庫，可以以更壓縮的方式處理日誌，需要定期進行記錄備份。
• 簡單模式，也有記錄檔，只是該模式下可以通過checkpoint自動重用virtual log file，所以記錄檔會處於一直重複使用的過程，保持一定大小，但是，如果有一個事務啟動，很久沒有commit，那麼從這個事務開始到最後commit的時間段內的交易記錄儲存空間都無法checpoint自動重用，這時，你很可能看到一個很大的記錄檔；注意，簡單模式下是無法進行記錄備份。

    資料庫裡邊，任何對資料庫的讀寫都是在記憶體頁中找到對應的資料也，再做修改，如果記憶體頁中不存在資料頁，則從磁碟載入如記憶體中。當一個修改操作發生時，修改的將是記憶體頁中對應的資料頁面，同時也會即時記錄到日後檔案ldf中。那麼，什麼時候資料會被同步到mdf檔案呢，只有以下三種情況：

• 做checkpoint時，後續會專門整理checkpoint的相應文章；
• Lazy write運行時，即記憶體出現壓力，需要把記憶體中的資料頁寫入到磁碟，騰出記憶體空間；
• eager write時，即發生bulk insert和select into操作時。

    DB中的交易記錄記錄，可以給我們帶來很多好處，它可以支援以下操作：

• 恢複個別的事務。
• 在 SQL Server 啟動時恢複所有未完成的事務。
• 將還原的資料庫、檔案、檔案組或頁前滾至故障點。
• 支援事務複製。
• 支援高可用性和災難恢複解決方案：AlwaysOn 可用性群組、資料庫鏡像和記錄傳送。

2 對資料庫啟動的影響    當資料庫重啟或者還原到最後的時候，資料庫都會進入 recovery狀態，正常情況下，這個狀態期間在幾十秒間，但是特殊情況下，它會花費非常長的時間，甚至幾個小時，如果這個步驟失敗，資料庫則進入到掛起 suspect狀態，無法正常提供使用。     那麼，當資料庫進入 recovery 的時候，它在操作些什麼呢？     SQL SERVER日誌會記錄所有修改記錄（資料的修改情況，不包含SQL語句），包括Begin Transaction和Commit / Rollback Transaction 操作。由於對交易記錄的修改，要比資料檔案的修改要快，所有會出現，資料修改更新到了記錄檔，但是還沒有落盤到資料檔案，那麼這個時候資料庫就處於recovery狀態，同時對交易記錄最近的一個checkpoint點以後的所有資料修改記錄做以下檢查：     所有檢查結束後，則會對資料庫做一個checkpoint的表示，並寫入交易記錄中，表明記錄檔跟資料檔案已經同步結束，完成了recovery過程，資料庫可正常提供使用。這裡需要注意一點，如果你資料庫最近一次checkpoint到現在的修改操作足夠多，那麼將會耗費相對較長時間來檢查，同時也能夠在 error log中看到百分比標識的recovery完成進展，避免漫無目的的等待。Error Log的檢查，可以通過圖形介面（見）查看當前日誌，也可以運行xp_readerrorlog 查詢。

 1 /* 2 xp_readerrorlog 3 1. 存檔編號 4 2. 日誌類型(1為SQL Server日誌,2為SQL Agent日誌) 5 3. 查詢包含的字串 6 4. 查詢包含的字串 7 5. LogDate開始時間 8 6. LogDate結束時間 9 7. 結果排序,按LogDate排序(可以為降序"Desc" Or 升序"Asc")，預設升序10 */11  12 Exec xp_readerrorlog 0,1,Null,Null,‘2017-02-16 10:53:32.300‘,‘2017-02-16 12:53:32.300‘

 

     假設出現這種情況，由於上線的重要程度遠遠重要過 資料丟失的情況，並且你跟所有部門溝通確認 可以承擔 data file跟log file之間的差異資料的丟失，那麼你可以按以下步驟操作， 嚴重建議不要這麼操作，因為會帶來不可預估的資料丟失情況， 如果你命懸一線，真打算放棄這部分資料，那麼，可以按照以下操作：

 1 #設定資料庫單使用者 2 alter database backupdb set single_user with rollback immediate 3  4 #設定資料庫緊急狀態 5 alter database backupdb set emergency with rollback immediate 6  7 #擷取交易記錄的實體名稱和邏輯名後，重建記錄檔 8 select name,physical_name from sys.master_fiels where database_id=db_id(‘backupdb‘) 9 alter database backupdb rebuild log on (name=‘交易記錄的邏輯名‘,filename=‘交易記錄的實體名稱詞‘)10 11 #設定資料庫online12 alter database backupdb set online with rollback immediate13 14 #設定資料庫為多使用者15 alter database backupdb set multi_user with rollback immediate

3 記錄檔添加方式    日誌記錄在 尾碼名為 ldf的檔案，允許有多個記錄檔，但是不會並發分開記錄日誌，而是使用填充滿一個記錄檔後，再轉向一個記錄檔，線性動作記錄檔案。    可以通過下方來添加 ldf檔案，需要注意幾個地方：

• 初始大小，建議直接設定為 截斷日誌的期間內最大值，比如，某DB 復原模式是完整模式，每隔半個小時做一次交易記錄備份且截斷日誌，那麼設定 記錄檔大小的時候，取業務高峰期 每半小時的日誌增長 最大值是5G，則可設定初始大小為 5G-7G之間；
• 增長大小，無論是 按比例增長還是按照MB增長，都不要設定過小，建議每次增長在100Mb左右，減少使用到自動成長，在最初設定的初始大小就滿足其增長需求 ，如果開始設定的 初始大小 偏小，不滿足，可以挑一個業務低峰期，修改變大初始大小。每一次檔案自動成長期間，都會對寫入的日誌造成堵塞，雖然時間很短，但是如果增長頻繁，則會影響資料庫操作；
• 自動成長，建議設定為自動成長，但是前提定期監控日誌的增長情況，避免磁碟空間不足，同時，如果復原模式是 完整模式或者大容量模式，還需定期做記錄備份截斷日誌，避免 交易記錄已滿的9002錯誤；
• 直接選取，建議與 mdf 檔案放在不同的磁碟上，分散IO，若是磁碟讀寫瓶頸不大，則可放在一個磁碟上；

添加方式有2種，如下：

 1 USE [master] 2 GO 3 ALTER DATABASE [backupdb] 4 ADD LOG FILE ( 5                NAME = N‘backupdb_log_1‘, 6                FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\backupdb_log_1.ldf‘ , 7                         SIZE = 524288KB , 8                         MAXSIZE = 1048576KB , 9                         FILEGROWTH = 10240KB10                       )11 GO

4 物理結構      資料庫的交易記錄映射在一個或者多個的物理檔案上，從概念上講，記錄檔是一系列的日誌記錄；從物理上講，日誌記錄序列被有效儲存在實現交易記錄的物理檔案中。    SQL Server 資料庫引擎在內部將每一物理記錄檔分成多個虛擬記錄檔，即VLF（Virtual Log File），虛擬記錄檔沒有固定大小，且物理記錄檔所包含的虛擬記錄檔數不固定。資料庫引擎在建立或擴充記錄檔時 動態選擇虛擬記錄檔的大小。資料庫引擎嘗試維護少量的虛擬檔案。在擴充記錄檔後，虛擬檔案的大小是現有日誌大小和新檔案增量大小之和。    只有當記錄檔使用較小的 size 和 growth_increment 值定義時，虛擬記錄檔才會影響系統效能。如果這些記錄檔由於許多微小增量而增長到很大，則它們將具有很多虛擬記錄檔。這會降低資料庫啟動以及記錄備份和還原作業的速度。 建議您為記錄檔分配一個接近於最終所需大小的size值，並且還要分配一個相對較大的 growth_increment 值。    管理員不能配置或設定虛擬記錄檔的大小或數量，但是 在VLF影響系統效能的情況下，可以嘗試縮小，通過收縮記錄檔的方式 。dbcc loginfo(dbname) 返回的行數，即為 VLF 檔案個數，當status為0時，即該檔案沒有被使用，還能寫入LOG，2表示已被使用，並且無法重用，這個時候，可以通過 backup Log 的形式，備份並截斷LOG檔案，則可以回收 從最後一個0到最近一個2行的空間。收縮記錄檔或者減少VLF檔案的方式如下，需要先備份記錄檔，才可以有效進行收縮，在沒有備份記錄檔的情況下，進行收縮，效果不大。詳見以下代碼： 

 1 #其行數及為VLF個數，status為0表示檔案未用，為2表示已被使用，無法重用 2 dbcc loginfo  3  4 #備份日誌 5 BACKUP LOG [backupdb] 6 TO  DISK = N‘D:\data\20170215_backupdb_log.trn‘ WITH NOFORMAT, NOINIT,  NAME = N‘backupdb-交易記錄  備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10 7 GO 8  9 #收縮記錄檔，根據記錄檔名來收縮500Mb，建議收縮大小是合理大小，參考上文的 初始大小 判斷10 USE [backupdb]11 GO12 SELECT name FROM sys.database_files WHERE type_desc=‘log‘13 DBCC SHRINKFILE (N‘jiankong_db_log‘ , 500)14 GO15 16 #其行數及為VLF個數，VLF檔案減少17 dbcc loginfo

    交易記錄是一種迴繞的檔案。假設，資料庫backupdb只有一個ldf檔案，且剛好分成了5個虛擬日誌，當我們開始使用資料庫的時候，邏輯日誌從物理日誌的最開始端向末端記錄，如。

    當出現checkpoint的時候，則會標註 最小恢複記錄序號 MinLSN，“MinLSN”是成功進行資料庫範圍內復原所需的最早日誌記錄的記錄序號。如。

     在MinLSN之前的所有虛擬記錄檔VLF都可以被截斷，資料庫會在以下兩個事件後自行截斷日誌：

• 簡單復原模式下，在檢查點之後發生。

• 在完整復原模式或大量記錄復原模式下，如果自上一次備份後產生檢查點，則在記錄備份後進行截斷（除非是僅複製記錄備份）。

    當截斷日誌的時候，這些VLF就可以被釋放回收，邏輯日誌的開頭也會移動到最後一個被階段VLF檔案末端。     假設這個時候，在MinLSN位置後，發生了一個事務，一直沒有commit，導致VLF3，VLF4，VLF5都被使用，那麼就會重用之前回收的VLF檔案。      正常情況下，如果經常截斷舊的日誌記錄，保持邏輯日誌的末端不到達邏輯日誌的開頭，滿足下一個檢查點之前船艦的所有新日誌記錄都有足夠的空間儲存，那麼記錄檔將永遠不會被填滿，保持一定的大小，可通過定期備份交易記錄來達到。    但是如果，邏輯日誌的結尾跟開頭碰面了，那麼當磁碟空間足夠的情況下，則按照 自動增大大小 指定的數量 增大記錄檔，並在 物理記錄檔中添加多個VLF檔案；如果磁碟空間不足，比指定的 增量大小 要少，那麼就會報錯，出現9002錯誤，導致資料庫無法進行所有寫操作。 5 延遲日誌截斷原因     日誌截斷會由於多種因素髮生延遲。可查詢sys.databases目錄檢視的 log_reuse_wait 和 log_reuse_wait_desc 列來發現是什麼（如果有）阻止了日誌的截斷。 下表對這些列的值進行了說明。  6 管理交易記錄    定期監控記錄檔的大小跟實際使用大小，以防日誌增長異常，佔滿磁碟空間，可通過以下兩種方式查看 記錄檔使用方式.

1 #查看日誌使用方式，檔案大小及實際使用大小2 dbcc sqlperf(logspace)3 4 #查看檔案相關資訊5 select name,physical_name,size*8.0/1024 size_Mb,* from sys.database_files

    定期記錄備份，兩個備份的間隔是運行遺失資料的時間跨度，不要過於頻繁備份，會對資料庫IO造成一定影響。

1 BACKUP LOG [backupdb]2 TO  DISK = N‘D:\data\20170215_backupdb_log.trn‘ WITH NOFORMAT, NOINIT,  NAME = N‘backupdb-交易記錄  備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 103 GO

    事務儘可能短，避免長時間開啟事務，或者忘記commit/roll back；    解決交易記錄已滿問題（9002錯誤）

• 若是限制了檔案最大值，在磁碟空間有剩餘的情況下，增加記錄檔的大小。
• 釋放磁碟空間以便日誌可以自動成長。
• 在其他磁碟上添加記錄檔。
• 備份日誌後，收縮日誌。
• 將記錄檔移到具有足夠空間的磁碟機。

 

 

參考文檔：https://msdn.microsoft.com/zh-cn/library/ms189275.aspxhttps://msdn.microsoft.com/zh-cn/library/ms190925(v=sql.110).aspxhttps://msdn.microsoft.com/zh-cn/library/ms190422(v=sql.110).aspxhttps://blogs.msdn.microsoft.com/apgcdsd/2011/12/29/943/https://msdn.microsoft.com/zh-cn/library/ms365418(v=sql.110).aspxhttps://msdn.microsoft.com/zh-cn/library/ms175495(v=sql.110).aspx 

SQL SERVER的交易記錄