DBI302-深入了解SQLServer交易紀(jì)錄

1、深入了解 SQL Server 交易紀(jì)錄胡百敬 合夥顧問集英信誠(chéng)DBI302大綱交易紀(jì)錄錄簡(jiǎn)介CheckpointVirtual LogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫(kù)復(fù)復(fù)原模式式交易紀(jì)錄錄備份Bulk交易紀(jì)錄錄維護(hù)交易易紀(jì)錄循環(huán)使用用交易紀(jì)錄錄資料檔案資料存放放的地方方一定在某某個(gè)檔案案群組內(nèi)內(nèi)考慮使用用兩個(gè)以以上的檔檔案群組組，Primary檔案群組組存放系系統(tǒng)物件件隨機(jī)讀寫寫交易紀(jì)錄錄檔完成成寫入後後，才會(huì)會(huì)到此更更新資料變更更會(huì)寫入入兩次，先交易易紀(jì)錄，後資料料檔案交易紀(jì)錄錄檔擷取資料料庫(kù)變動(dòng)動(dòng)的狀態(tài)態(tài)一定先更更動(dòng)此，再更新新到資料料檔維護(hù)交易易的Atomicity和Durab

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

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

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

5、Undo交易的所所有資訊訊大綱交易紀(jì)錄錄簡(jiǎn)介CheckpointVirtual LogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫(kù)復(fù)復(fù)原模式式交易紀(jì)錄錄備份Bulk交易紀(jì)錄錄維護(hù)交易易紀(jì)錄循環(huán)使用用交易紀(jì)錄錄資料庫(kù)復(fù)復(fù)原模式式SimpleCheckpoint 發(fā)生時(shí)清掉交易紀(jì)錄Bulk Logged備份交易紀(jì)錄後清掉交易紀(jì)錄部分動(dòng)作可以使用最少量的交易紀(jì)錄Full備份交易紀(jì)錄後清掉交易紀(jì)錄所有的行為都有完整的紀(jì)錄預(yù)設(shè)值交易紀(jì)錄錄備份將Redo和Undo不再需要要的交易易紀(jì)錄存存放到備備份檔中中，並從從交易紀(jì)紀(jì)錄檔移移除搭配完整整備份，以還原原資料庫(kù)庫(kù)到特定定時(shí)間點(diǎn)點(diǎn)Simple復(fù)原模式式無(wú)法備備份

6、交易易紀(jì)錄不保證交交易紀(jì)錄錄空間會(huì)會(huì)被重用用不會(huì)縮小小交易紀(jì)紀(jì)錄檔案案Bulk Logged特定的行行為可以以讓SQLServer僅記錄發(fā)發(fā)生該行行為以及及使用的的空間可以讓動(dòng)動(dòng)作執(zhí)行行得較快快注意：在在Bulk Logged動(dòng)作完成成交易前前，必須須將受影影響的資資料頁(yè)內(nèi)內(nèi)容寫入入資料檔檔交易紀(jì)錄錄檔成長(zhǎng)長(zhǎng)得較小小，但交交易紀(jì)錄錄備份檔檔不會(huì)較較小DemoBulk Logged行為大綱交易紀(jì)錄錄簡(jiǎn)介CheckpointVirtual LogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫(kù)復(fù)復(fù)原模式式交易紀(jì)錄錄備份Bulk交易紀(jì)錄錄維護(hù)交易易紀(jì)錄循環(huán)使用用交易紀(jì)錄錄記錄的行行為我們希望望交易紀(jì)紀(jì)錄檔有

7、有限地成成長(zhǎng)一旦不再再需要局局部的紀(jì)紀(jì)錄，期期待該空空間可以以回收使使用交易紀(jì)錄錄有循環(huán)環(huán)的特徵徵，應(yīng)可可以重用用空間DBA負(fù)有管好好交易紀(jì)紀(jì)錄的責(zé)責(zé)任什麼是合合理的交交易紀(jì)錄錄檔成長(zhǎng)長(zhǎng)量，異異常成長(zhǎng)長(zhǎng)的原因因?yàn)楹?？交易區(qū)塊塊(Log Block)實(shí)體寫入入到交易易紀(jì)錄檔檔的單位位大小從512位元組到到60K在記憶體中中以“l(fā)og buffer”儲(chǔ)存儲(chǔ)存在BufferPool交易完畢畢或?qū)憹M滿60K，就更新新到交易易紀(jì)錄檔檔較大的交交易量(接近60K)完成寫入入較有效效率有時(shí)多筆筆小交易易對(duì)效能能不利虛擬交易易紀(jì)錄檔檔(VirtualLogFile)在交易紀(jì)紀(jì)錄檔內(nèi)內(nèi)清除交交易的單單位在建立交

8、交易紀(jì)錄錄檔或增增長(zhǎng)檔案案大小時(shí)時(shí)產(chǎn)生資料庫(kù)內(nèi)內(nèi)有多少少數(shù)量的的VLF是有差異異的交易紀(jì)錄檔增長(zhǎng)增加 VLF 的量 64 MB 和 1 GB16需要多少少VLF檔案依需求！太多，傷傷讀取紀(jì)紀(jì)錄和還還原資料料庫(kù)的效效能太少，很很長(zhǎng)時(shí)間間才能釋釋放和重重用VLF區(qū)段，造造成交易易紀(jì)錄檔檔更多的的成長(zhǎng)若VLF檔案太多多，SQLServer會(huì)發(fā)出警警告Databasehasmorethan 1000virtuallogfileswhichisexcessive.Toomanyvirtual logfilescancauselongstartup andbackuptimes. Consider shr

9、inkingthelog andusinga differentgrowthincrement to reducethenumber of virtuallog files.LogSequenceNumber資料庫(kù)內(nèi)內(nèi)狀態(tài)變變化的識(shí)識(shí)別子每個(gè)交易紀(jì)錄的唯唯一識(shí)別別編碼單純遞增增VLF編號(hào)：交易區(qū)塊塊編號(hào)：交易紀(jì)錄錄編號(hào)DemoVLF格式與重重用交易易紀(jì)錄為何交易易紀(jì)錄檔檔如此大大很多朋友友的經(jīng)驗(yàn)驗(yàn)通常是Full復(fù)原模式式，但不不常做交交易紀(jì)錄錄備份可能巨大大交易造造成的紀(jì)紀(jì)錄有時(shí)需要要在其他他的磁碟碟機(jī)加交交易紀(jì)錄錄檔解法：修正其下的問題題縮小交易易紀(jì)錄檔檔將Log檔增大到到適當(dāng)?shù)牡拇笮≈赜肰

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

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

12、channel9./Blogs/Channel9TaiwanResourcesfor IT Professionalshttp:/social.technet./Forums/zh-tw/categories填妥大會(huì)會(huì)背包中中的大會(huì)會(huì)問卷，可於活活動(dòng)第三三天兌換換問卷禮禮哦！請(qǐng)協(xié)助完完成本本課程問問卷，並在離離開教室室時(shí)交給給工作人人員！感謝您的的合作。 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