DBI302-深入了解SQLServer交易纪录

1、深入了解 SQL Server 交易紀錄胡百敬 合夥顧問集英信誠DBI302大綱交易紀錄錄簡介CheckpointVirtual LogFiles結構VLF重用VLF破碎資料庫復復原模式式交易紀錄錄備份Bulk交易紀錄錄維護交易易紀錄循環使用用交易紀錄錄資料檔案資料存放放的地方方一定在某某個檔案案群組內內考慮使用用兩個以以上的檔檔案群組組，Primary檔案群組組存放系系統物件件隨機讀寫寫交易紀錄錄檔完成成寫入後後，才會會到此更更新資料變更更會寫入入兩次，先交易易紀錄，後資料料檔案交易紀錄錄檔擷取資料料庫變動動的狀態態一定先更更動此，再更新新到資料料檔維護交易易的Atomicity和Durab

2、ility循序寫入入，通常常也循序序讀出一般只需需要一個個交易紀紀錄檔交易紀錄錄先寫在更新資資料檔之之前，所所有的變變化須能能永久記記錄在交交易紀錄錄內Atomic變更能全全部完成成，或完完全沒變變交易紀錄錄能保證證回復可可行Durability變更能在在穩定的的媒體上上永久保保存放置交易易紀錄檔檔交易紀錄錄檔應該放放哪？專用的LUN通常是較較好的選選項如果存取取量高，需要專專屬的磁磁碟系統統RAID1或RAID 10提供保護護與效能能RAID5通常不利利於更新新儘量不要要與其他他檔案共共用不同的檔案放在在一起存存取，大大家都變變成隨機機存取SQLServer資料庫上上線資料庫需需要經過過一系

3、列列的動作作，才能能進入一一致的狀狀態一般稱為為Recovery需要交易易紀錄何時發生生：資料庫從從離線轉轉成線上附加資料料庫還原資料料庫服務停止止重起/叢集容錯錯移轉Recovery的階段分析讀取交易紀錄找尋進行中的交易和 Dirty Pages從最後一次的 Checkpoint 之後開始找尋從何開始 Redo 流程Redo重播所有完成但未更新到資料檔的交易若是 Enterprise 版本，則在這個時間點可以存取資料庫Undo回復資料庫損毀時，未完的交易可能需要多次讀取交易紀錄從最舊的交易紀錄開始Checkpoint確保Recovery的Redo階段最少少工作量將更新過過的資料料頁(dirt

4、ypage)寫入到磁磁碟內的的資料檔檔不管交易易狀態如如何，將將所有的的dirtypage寫入到資資料檔可能造成成大量的的I/O不會從Bufferpool移除資料料頁當有記憶憶體壓力力時，Lazy Writer執行此工工作Checkpoint相關選項項搭配sp_configure設定執行行個體recoveryinterval選項SQLServer2012後可設定定資料庫庫層級Target_Recovery_Time選項DemoCheckpoint交易紀錄錄保證資料料庫的交交易每筆紀錄透透過LogSequenceNumber唯一標示示不僅與完完成的交交易有關關記載所有的的變動包含可以以Redo和

5、Undo交易的所所有資訊訊大綱交易紀錄錄簡介CheckpointVirtual LogFiles結構VLF重用VLF破碎資料庫復復原模式式交易紀錄錄備份Bulk交易紀錄錄維護交易易紀錄循環使用用交易紀錄錄資料庫復復原模式式SimpleCheckpoint 發生時清掉交易紀錄Bulk Logged備份交易紀錄後清掉交易紀錄部分動作可以使用最少量的交易紀錄Full備份交易紀錄後清掉交易紀錄所有的行為都有完整的紀錄預設值交易紀錄錄備份將Redo和Undo不再需要要的交易易紀錄存存放到備備份檔中中，並從從交易紀紀錄檔移移除搭配完整整備份，以還原原資料庫庫到特定定時間點點Simple復原模式式無法備備份

6、交易易紀錄不保證交交易紀錄錄空間會會被重用用不會縮小小交易紀紀錄檔案案Bulk Logged特定的行行為可以以讓SQLServer僅記錄發發生該行行為以及及使用的的空間可以讓動動作執行行得較快快注意：在在Bulk Logged動作完成成交易前前，必須須將受影影響的資資料頁內內容寫入入資料檔檔交易紀錄錄檔成長長得較小小，但交交易紀錄錄備份檔檔不會較較小DemoBulk Logged行為大綱交易紀錄錄簡介CheckpointVirtual LogFiles結構VLF重用VLF破碎資料庫復復原模式式交易紀錄錄備份Bulk交易紀錄錄維護交易易紀錄循環使用用交易紀錄錄記錄的行行為我們希望望交易紀紀錄檔有

7、有限地成成長一旦不再再需要局局部的紀紀錄，期期待該空空間可以以回收使使用交易紀錄錄有循環環的特徵徵，應可可以重用用空間DBA負有管好好交易紀紀錄的責責任什麽是合合理的交交易紀錄錄檔成長長量，異異常成長長的原因因為何？交易區塊塊(Log Block)實體寫入入到交易易紀錄檔檔的單位位大小從512位元組到到60K在記憶體中中以“log buffer”儲存儲存在BufferPool交易完畢畢或寫滿滿60K，就更新新到交易易紀錄檔檔較大的交交易量(接近60K)完成寫入入較有效效率有時多筆筆小交易易對效能能不利虛擬交易易紀錄檔檔(VirtualLogFile)在交易紀紀錄檔內內清除交交易的單單位在建立交

8、交易紀錄錄檔或增增長檔案案大小時時產生資料庫內內有多少少數量的的VLF是有差異異的交易紀錄檔增長增加 VLF 的量 64 MB 和 1 GB16需要多少少VLF檔案依需求！太多，傷傷讀取紀紀錄和還還原資料料庫的效效能太少，很很長時間間才能釋釋放和重重用VLF區段，造造成交易易紀錄檔檔更多的的成長若VLF檔案太多多，SQLServer會發出警警告Databasehasmorethan 1000virtuallogfileswhichisexcessive.Toomanyvirtual logfilescancauselongstartup andbackuptimes. Consider shr

9、inkingthelog andusinga differentgrowthincrement to reducethenumber of virtuallog files.LogSequenceNumber資料庫內內狀態變變化的識識別子每個交易紀錄的唯唯一識別別編碼單純遞增增VLF編號：交易區塊塊編號：交易紀錄錄編號DemoVLF格式與重重用交易易紀錄為何交易易紀錄檔檔如此大大很多朋友友的經驗驗通常是Full復原模式式，但不不常做交交易紀錄錄備份可能巨大大交易造造成的紀紀錄有時需要要在其他他的磁碟碟機加交交易紀錄錄檔解法：修正其下的問題題縮小交易易紀錄檔檔將Log檔增大到到適當的的大小重用V

10、LF重用VLF需要：在當下或或之前的的VLFs沒有還在在進行中中的交易易沒有需要要完成其其他工作作的交易易紀錄，例如還還未複寫寫資料庫復復原模式式為Simple時，在Checkpoint後可重用用資料庫復復原模式式為Bulk Logged或Full需要在備份交易紀錄錄後Log_reuse_wait_desc在sys.databases內的欄位位傳會當下下紀錄未未重用的原原因，確確認交易紀紀錄為何何留著可能值如如右表NothingCheckpointLog backupActive backup or restoreActive TransactionDatabase MirroringRepl

11、icationDatabase snapshot creationLog ScanOther縮小交易易紀錄檔檔縮小交易易紀錄檔檔案大小小使用DBCC SHRINKFILE確定縮小小交易紀紀錄檔後後，再擴擴增到適適當大小小縮小只能能到某個個VLF的邊界DemoVLF過多與破破碎Q&AmsdnResourcesfor Developershttp:/social.msdn./Forums/zh-tw/categories/LearningMicrosoftCertification& Training ResourcesTechNet相關資源源Connect.Share.Discusshttp:/

12、channel9./Blogs/Channel9TaiwanResourcesfor IT Professionalshttp:/social.technet./Forums/zh-tw/categories填妥大會會背包中中的大會會問卷，可於活活動第三三天兌換換問卷禮禮哦！請協助完完成本本課程問問卷，並在離離開教室室時交給給工作人人員！感謝您的的合作。 2013Microsoft Corporation.Allrights reserved.Microsoft, Windowsand other productnamesare or mayberegistered trademarksand/or trademarksinthe U.S.and/or other countries.Theinformationherein is forinformational purposes onlyand representsthe currentviewofMicrosoftCorporationasofthe dateofthispresentation.Because Microsoftmust respondtochangingmarket conditions, it shouldnotbeinterpretedtobeacommitment on thepart