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

深入了解SQLServer交易紀(jì)錄胡百敬合夥顧問集英信誠DBI302

深入了解SQLServer交易紀(jì)錄胡百敬http:大綱交易紀(jì)錄簡介CheckpointVirtualLogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫復(fù)原模式交易紀(jì)錄備份Bulk交易紀(jì)錄維護(hù)交易

紀(jì)錄循環(huán)使用交易紀(jì)錄大綱交易紀(jì)錄簡介VirtualLogFiles結(jié)構(gòu)資料資料檔案資料存放的地方一定在某個(gè)檔案群組內(nèi)考慮使用兩個(gè)以上的檔案群組，Primary檔案群組存放系統(tǒng)物件隨機(jī)讀寫交易紀(jì)錄檔完成寫入後，才會(huì)到此更新資料變更會(huì)寫入兩次，先交易紀(jì)錄，後資料檔案資料檔案資料存放的地方交易紀(jì)錄檔擷取資料庫變動(dòng)的狀態(tài)一定先更動(dòng)此，再更新到資料檔維護(hù)交易的Atomicity

和Durability循序?qū)懭?，通常也循序讀出一般只需要一個(gè)交易紀(jì)錄檔交易紀(jì)錄檔擷取資料庫變動(dòng)的狀態(tài)交易紀(jì)錄先寫在更新資料檔之前，所有的變化須能永久記錄在交易紀(jì)錄內(nèi)Atomic變更能全部完成，或完全沒變交易紀(jì)錄能保證回復(fù)可行Durability變更能在穩(wěn)定的媒體上永久保存交易紀(jì)錄先寫在更新資料檔之前，所有的變化須能永久記錄在交易紀(jì)放置交易紀(jì)錄檔交易紀(jì)錄檔應(yīng)該放哪？專用的LUN通常是較好的選項(xiàng)如果存取量高，需要專屬的磁碟系統(tǒng)RAID

1或RAID10提供保護(hù)與效能RAID

5通常不利於更新儘量不要與其他檔案共用不同的檔案放在一起存取，大家都變成隨機(jī)存取放置交易紀(jì)錄檔交易紀(jì)錄檔應(yīng)該放哪？SQL

Server資料庫上線資料庫需要經(jīng)過一系列的動(dòng)作，才能進(jìn)入一致的狀態(tài)一般稱為Recovery需要交易紀(jì)錄何時(shí)發(fā)生：資料庫從離線轉(zhuǎn)成線上附加資料庫還原資料庫服務(wù)停止重起/叢集容錯(cuò)移轉(zhuǎn)SQLServer資料庫上線資料庫需要經(jīng)過一系列的動(dòng)作，Recovery的階段分析讀取交易紀(jì)錄找尋進(jìn)行中的交易和DirtyPages從最後一次的Checkpoint之後開始找尋從何開始Redo流程Redo重播所有完成但未更新到資料檔的交易若是Enterprise版本，則在這個(gè)時(shí)間點(diǎn)可以存取資料庫Undo回復(fù)資料庫損毀時(shí)，未完的交易可能需要多次讀取交易紀(jì)錄從最舊的交易紀(jì)錄開始Recovery的階段分析讀取交易紀(jì)錄找尋進(jìn)行中的交易和Checkpoint確保Recovery的Redo階段最少工作量將更新過的資料頁(dirtypage)寫入到磁碟內(nèi)的資料檔不管交易狀態(tài)如何，將所有的dirtypage寫入到資料檔可能造成大量的I/O不會(huì)從Bufferpool移除資料頁當(dāng)有記憶體壓力時(shí)，LazyWriter執(zhí)行此工作Checkpoint確保Recovery的Redo階Checkpoint相關(guān)選項(xiàng)搭配sp_configure設(shè)定執(zhí)行個(gè)體recoveryinterval選項(xiàng)SQL

Server2012後可設(shè)定資料庫層級(jí)

Target_Recovery_Time選項(xiàng)Checkpoint相關(guān)選項(xiàng)搭配sp_configureDemoCheckpointDemoCheckpoint交易紀(jì)錄保證資料庫的交易每筆紀(jì)錄透過LogSequenceNumber唯一標(biāo)示不僅與完成的交易有關(guān)記載所有的變動(dòng)包含可以Redo和Undo交易的所有資訊交易紀(jì)錄保證資料庫的交易大綱交易紀(jì)錄簡介CheckpointVirtualLogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫復(fù)原模式交易紀(jì)錄備份Bulk交易紀(jì)錄維護(hù)交易

紀(jì)錄循環(huán)使用交易紀(jì)錄大綱交易紀(jì)錄簡介VirtualLogFiles結(jié)構(gòu)資料資料庫復(fù)原模式SimpleCheckpoint發(fā)生時(shí)清掉交易紀(jì)錄BulkLogged備份交易紀(jì)錄後清掉交易紀(jì)錄部分動(dòng)作可以使用最少量的交易紀(jì)錄Full備份交易紀(jì)錄後清掉交易紀(jì)錄所有的行為都有完整的紀(jì)錄預(yù)設(shè)值資料庫復(fù)原模式SimpleCheckpoint發(fā)生時(shí)清掉交交易紀(jì)錄備份將Redo和Undo不再需要的交易紀(jì)錄存放到備份檔中，並從交易紀(jì)錄檔移除搭配完整備份，以還原資料庫到特定時(shí)間點(diǎn)Simple復(fù)原模式無法備份交易紀(jì)錄不保證交易紀(jì)錄空間會(huì)被重用不會(huì)縮小交易紀(jì)錄檔案交易紀(jì)錄備份將Redo和Undo不再需要的交易紀(jì)錄存BulkLogged特定的行為可以讓SQLServer僅記錄發(fā)生該行為以及使用的空間可以讓動(dòng)作執(zhí)行得較快注意：在BulkLogged動(dòng)作完成交易前，必須將受影響的資料頁內(nèi)容寫入資料檔交易紀(jì)錄檔成長得較小，但交易紀(jì)錄備份檔不會(huì)較小BulkLogged特定的行為可以讓SQLServerDemoBulkLogged行為DemoBulkLogged行為大綱交易紀(jì)錄簡介CheckpointVirtualLogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫復(fù)原模式交易紀(jì)錄備份Bulk交易紀(jì)錄維護(hù)交易

紀(jì)錄循環(huán)使用交易紀(jì)錄大綱交易紀(jì)錄簡介VirtualLogFiles結(jié)構(gòu)資料記錄的行為我們希望交易紀(jì)錄檔有限地成長一旦不再需要局部的紀(jì)錄，期待該空間可以回收使用交易紀(jì)錄有循環(huán)的特徵，應(yīng)可以重用空間DBA負(fù)有管好交易紀(jì)錄的責(zé)任什麼是合理的交易紀(jì)錄檔成長量，異常成長的原因?yàn)楹?？記錄的行為我們希望交易紀(jì)錄檔有限地成長交易區(qū)塊(LogBlock)實(shí)體寫入到交易紀(jì)錄檔的單位大小從512位元組到60K在記憶體中以“l(fā)ogbuffer”儲(chǔ)存儲(chǔ)存在BufferPool交易完畢或?qū)憹M60K，就更新到交易紀(jì)錄檔較大的交易量(接近60K)完成寫入較有效率有時(shí)多筆小交易對(duì)效能不利交易區(qū)塊(LogBlock)實(shí)體寫入到交易紀(jì)錄檔的單位虛擬交易紀(jì)錄檔(VirtualLogFile)在交易紀(jì)錄檔內(nèi)清除交易的單位在建立交易紀(jì)錄檔或增長檔案大小時(shí)產(chǎn)生資料庫內(nèi)有多少數(shù)量的VLF是有差異的交易紀(jì)錄檔增長增加VLF的量<=

64MB4>64MB和<=1GB8>1GB16虛擬交易紀(jì)錄檔(VirtualLogFile)在交易紀(jì)錄需要多少VLF檔案依需求！太多，傷讀取紀(jì)錄和還原資料庫的效能太少，很長時(shí)間才能釋放和重用VLF區(qū)段，造成交易紀(jì)錄檔更多的成長若VLF檔案太多，SQL

Server會(huì)發(fā)出警告Database…h(huán)asmorethan1000virtuallogfileswhichisexcessive.Toomanyvirtuallogfilescancauselongstartupandbackuptimes.Considershrinkingthelogandusingadifferentgrowthincrementtoreducethenumberofvirtuallogfiles.需要多少VLF檔案依需求！Database…h(huán)asLogSequenceNumber資料庫內(nèi)狀態(tài)變化的識(shí)別子每個(gè)交易紀(jì)錄的唯一識(shí)別編碼單純遞增VLF編號(hào)：交易區(qū)塊編號(hào)：交易紀(jì)錄編號(hào)LogSequenceNumber資料庫內(nèi)狀態(tài)變化的識(shí)別DemoVLF格式與重用交易紀(jì)錄DemoVLF格式與重用交易紀(jì)錄為何交易紀(jì)錄檔如此大很多朋友的經(jīng)驗(yàn)通常是Full復(fù)原模式，但不常做交易紀(jì)錄備份可能巨大交易造成的紀(jì)錄有時(shí)需要在其他的磁碟機(jī)加交易紀(jì)錄檔解法：修正其下的問題縮小交易紀(jì)錄檔將Log檔增大到適當(dāng)?shù)拇笮楹谓灰准o(jì)錄檔如此大很多朋友的經(jīng)驗(yàn)解法：重用VLF重用VLF需要：在當(dāng)下或之前的VLFs沒有還在進(jìn)行中的交易沒有需要完成其他工作的交易紀(jì)錄，例如還未複寫資料庫復(fù)原模式為Simple時(shí)，在Checkpoint後可重用資料庫復(fù)原模式為BulkLogged或Full需要在備份交易紀(jì)錄後重用VLF重用VLF需要：Log_reuse_wait_desc在sys.databases

內(nèi)的欄位傳會(huì)當(dāng)下紀(jì)錄未

重用的原因，確

認(rèn)交易紀(jì)錄為何

留著可能值如右表NothingCheckpointLogbackupActivebackuporrestoreActiveTransactionDatabaseMirroringReplicationDatabasesnapshotcreationLogScanOtherLog_reuse_wait_desc在sys.datab縮小交易紀(jì)錄檔縮小交易紀(jì)錄檔案大小使用DBCCSHRINKFILE確定縮小交易紀(jì)錄檔後，再擴(kuò)增到適當(dāng)大小縮小只能到某個(gè)VLF的邊界縮小交易紀(jì)錄檔縮小交易紀(jì)錄檔案大小DemoVLF過多與破碎DemoVLF過多與破碎Q&AQ&AmsdnResourcesforDevelopers/

Forums/zh-tw/categories/LearningMicrosoftCertification&TrainingResourcesTechNet相關(guān)資源Connect.Share.Discuss/Blogs/Channel9TaiwanResourcesforITProfessionals/

Forums/zh-tw/categoriesmsdnResourcesforDevelopersht填妥大會(huì)背包中的大會(huì)問卷，可於活動(dòng)第三天兌換問卷禮哦！

請(qǐng)協(xié)助完成「本課程問卷」，並在離開教室時(shí)交給工作人員！感謝您的合作。填妥大會(huì)背包中的大會(huì)問卷，可於活動(dòng)第三天兌?2013MicrosoftCorporation.Allrightsreserved.Microsoft,Windowsandotherproductnamesareormayberegisteredtrademarksand/ortrademarksintheU.S.and/orothercountries.TheinformationhereinisforinformationalpurposesonlyandrepresentsthecurrentviewofMicrosoftCorporationasofthedateofthispresentation.BecauseMicrosoftmustrespondtochangingmarketconditions,itshouldnotbeinterpretedtobeacommitmentonthepartofMicrosoft,andMicrosoftcannotguaranteetheaccuracyofanyinformationprovidedafterthedateofthispresentation.MICROSOFTMAKESNOWARRANTIES,EXPRESS,IMPLIEDORSTATUTORY,ASTOTHEINFORMATIONINTHISPRESENTATION.?2013MicrosoftCorporation.

深入了解SQLServer交易紀(jì)錄胡百敬合夥顧問集英信誠DBI302

深入了解SQLServer交易紀(jì)錄胡百敬http:大綱交易紀(jì)錄簡介CheckpointVirtualLogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫復(fù)原模式交易紀(jì)錄備份Bulk交易紀(jì)錄維護(hù)交易

紀(jì)錄循環(huán)使用交易紀(jì)錄大綱交易紀(jì)錄簡介VirtualLogFiles結(jié)構(gòu)資料資料檔案資料存放的地方一定在某個(gè)檔案群組內(nèi)考慮使用兩個(gè)以上的檔案群組，Primary檔案群組存放系統(tǒng)物件隨機(jī)讀寫交易紀(jì)錄檔完成寫入後，才會(huì)到此更新資料變更會(huì)寫入兩次，先交易紀(jì)錄，後資料檔案資料檔案資料存放的地方交易紀(jì)錄檔擷取資料庫變動(dòng)的狀態(tài)一定先更動(dòng)此，再更新到資料檔維護(hù)交易的Atomicity

和Durability循序?qū)懭耄ǔＲ惭蜃x出一般只需要一個(gè)交易紀(jì)錄檔交易紀(jì)錄檔擷取資料庫變動(dòng)的狀態(tài)交易紀(jì)錄先寫在更新資料檔之前，所有的變化須能永久記錄在交易紀(jì)錄內(nèi)Atomic變更能全部完成，或完全沒變交易紀(jì)錄能保證回復(fù)可行Durability變更能在穩(wěn)定的媒體上永久保存交易紀(jì)錄先寫在更新資料檔之前，所有的變化須能永久記錄在交易紀(jì)放置交易紀(jì)錄檔交易紀(jì)錄檔應(yīng)該放哪？專用的LUN通常是較好的選項(xiàng)如果存取量高，需要專屬的磁碟系統(tǒng)RAID

1或RAID10提供保護(hù)與效能RAID

5通常不利於更新儘量不要與其他檔案共用不同的檔案放在一起存取，大家都變成隨機(jī)存取放置交易紀(jì)錄檔交易紀(jì)錄檔應(yīng)該放哪？SQL

Server資料庫上線資料庫需要經(jīng)過一系列的動(dòng)作，才能進(jìn)入一致的狀態(tài)一般稱為Recovery需要交易紀(jì)錄何時(shí)發(fā)生：資料庫從離線轉(zhuǎn)成線上附加資料庫還原資料庫服務(wù)停止重起/叢集容錯(cuò)移轉(zhuǎn)SQLServer資料庫上線資料庫需要經(jīng)過一系列的動(dòng)作，Recovery的階段分析讀取交易紀(jì)錄找尋進(jìn)行中的交易和DirtyPages從最後一次的Checkpoint之後開始找尋從何開始Redo流程Redo重播所有完成但未更新到資料檔的交易若是Enterprise版本，則在這個(gè)時(shí)間點(diǎn)可以存取資料庫Undo回復(fù)資料庫損毀時(shí)，未完的交易可能需要多次讀取交易紀(jì)錄從最舊的交易紀(jì)錄開始Recovery的階段分析讀取交易紀(jì)錄找尋進(jìn)行中的交易和Checkpoint確保Recovery的Redo階段最少工作量將更新過的資料頁(dirtypage)寫入到磁碟內(nèi)的資料檔不管交易狀態(tài)如何，將所有的dirtypage寫入到資料檔可能造成大量的I/O不會(huì)從Bufferpool移除資料頁當(dāng)有記憶體壓力時(shí)，LazyWriter執(zhí)行此工作Checkpoint確保Recovery的Redo階Checkpoint相關(guān)選項(xiàng)搭配sp_configure設(shè)定執(zhí)行個(gè)體recoveryinterval選項(xiàng)SQL

Server2012後可設(shè)定資料庫層級(jí)

Target_Recovery_Time選項(xiàng)Checkpoint相關(guān)選項(xiàng)搭配sp_configureDemoCheckpointDemoCheckpoint交易紀(jì)錄保證資料庫的交易每筆紀(jì)錄透過LogSequenceNumber唯一標(biāo)示不僅與完成的交易有關(guān)記載所有的變動(dòng)包含可以Redo和Undo交易的所有資訊交易紀(jì)錄保證資料庫的交易大綱交易紀(jì)錄簡介CheckpointVirtualLogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫復(fù)原模式交易紀(jì)錄備份Bulk交易紀(jì)錄維護(hù)交易

紀(jì)錄循環(huán)使用交易紀(jì)錄大綱交易紀(jì)錄簡介VirtualLogFiles結(jié)構(gòu)資料資料庫復(fù)原模式SimpleCheckpoint發(fā)生時(shí)清掉交易紀(jì)錄BulkLogged備份交易紀(jì)錄後清掉交易紀(jì)錄部分動(dòng)作可以使用最少量的交易紀(jì)錄Full備份交易紀(jì)錄後清掉交易紀(jì)錄所有的行為都有完整的紀(jì)錄預(yù)設(shè)值資料庫復(fù)原模式SimpleCheckpoint發(fā)生時(shí)清掉交交易紀(jì)錄備份將Redo和Undo不再需要的交易紀(jì)錄存放到備份檔中，並從交易紀(jì)錄檔移除搭配完整備份，以還原資料庫到特定時(shí)間點(diǎn)Simple復(fù)原模式無法備份交易紀(jì)錄不保證交易紀(jì)錄空間會(huì)被重用不會(huì)縮小交易紀(jì)錄檔案交易紀(jì)錄備份將Redo和Undo不再需要的交易紀(jì)錄存BulkLogged特定的行為可以讓SQLServer僅記錄發(fā)生該行為以及使用的空間可以讓動(dòng)作執(zhí)行得較快注意：在BulkLogged動(dòng)作完成交易前，必須將受影響的資料頁內(nèi)容寫入資料檔交易紀(jì)錄檔成長得較小，但交易紀(jì)錄備份檔不會(huì)較小BulkLogged特定的行為可以讓SQLServerDemoBulkLogged行為DemoBulkLogged行為大綱交易紀(jì)錄簡介CheckpointVirtualLogFiles結(jié)構(gòu)VLF重用VLF破碎資料庫復(fù)原模式交易紀(jì)錄備份Bulk交易紀(jì)錄維護(hù)交易

紀(jì)錄循環(huán)使用交易紀(jì)錄大綱交易紀(jì)錄簡介VirtualLogFiles結(jié)構(gòu)資料記錄的行為我們希望交易紀(jì)錄檔有限地成長一旦不再需要局部的紀(jì)錄，期待該空間可以回收使用交易紀(jì)錄有循環(huán)的特徵，應(yīng)可以重用空間DBA負(fù)有管好交易紀(jì)錄的責(zé)任什麼是合理的交易紀(jì)錄檔成長量，異常成長的原因?yàn)楹?？記錄的行為我們希望交易紀(jì)錄檔有限地成長交易區(qū)塊(LogBlock)實(shí)體寫入到交易紀(jì)錄檔的單位大小從512位元組到60K在記憶體中以“l(fā)ogbuffer”儲(chǔ)存儲(chǔ)存在BufferPool交易完畢或?qū)憹M60K，就更新到交易紀(jì)錄檔較大的交易量(接近60K)完成寫入較有效率有時(shí)多筆小交易對(duì)效能不利交易區(qū)塊(LogBlock)實(shí)體寫入到交易紀(jì)錄檔的單位虛擬交易紀(jì)錄檔(VirtualLogFile)在交易紀(jì)錄檔內(nèi)清除交易的單位在建立交易紀(jì)錄檔或增長檔案大小時(shí)產(chǎn)生資料庫內(nèi)有多少數(shù)量的VLF是有差異的交易紀(jì)錄檔增長增加VLF的量<=

64MB4>64MB和<=1GB8>1GB16虛擬交易紀(jì)錄檔(VirtualLogFile)在交易紀(jì)錄需要多少VLF檔案依需求！太多，傷讀取紀(jì)錄和還原資料庫的效能太少，很長時(shí)間才能釋放和重用VLF區(qū)段，造成交易紀(jì)錄檔更多的成長若VLF檔案太多，SQL

Server會(huì)發(fā)出警告Database…h(huán)asmorethan1000virtuallogfileswhichisexcessive.Toomanyvirtuallogfilescancauselongstartupandbackuptimes.Considershrinkingthelogandusingadifferentgrowthincrementtoreducethenumberofvirtuallogfiles.需要多少VLF檔案依需求！Database…h(huán)asLogSequenceNumber資料庫內(nèi)狀態(tài)變化的識(shí)別子每個(gè)交易紀(jì)錄的唯一識(shí)別編碼單純遞增VLF編號(hào)：交易區(qū)塊編號(hào)：交易紀(jì)錄編號(hào)LogSequenceNumber資料庫內(nèi)狀態(tài)變化的識(shí)別DemoVLF格式與重用交易紀(jì)錄DemoVLF格式與重用交易紀(jì)錄為何交易紀(jì)錄檔如此大很多朋友的經(jīng)驗(yàn)通常是Full復(fù)原模式，但不常做交易紀(jì)錄備份可能巨大交易造成的紀(jì)錄有時(shí)需要在其他的磁碟機(jī)加交易紀(jì)錄檔解法：修正其下的問題縮小交易紀(jì)錄檔將Log檔增大到適當(dāng)?shù)拇笮楹谓灰准o(jì)錄檔如此大很多朋友的經(jīng)驗(yàn)解法：重用VLF重用VLF需要：在當(dāng)下或之前的VLFs沒有還在進(jìn)行中的交易沒有需要完成其他工作的交易紀(jì)錄，例如還未複寫資料庫復(fù)原模式為Simple時(shí)，在Checkpoint後可重用資料庫復(fù)原模式為BulkLogged或Full需要在備份交易紀(jì)錄後重用VLF重用VLF需要：Log_reuse_wait_desc在sys.databases

內(nèi)的欄位傳會(huì)當(dāng)下紀(jì)錄未

重用的原因，確

認(rèn)交易紀(jì)錄為何

留著可能值如右表NothingCheckpointLogbackupActivebackuporrestoreActiveTransactionDatabaseMirroringReplicationDatabasesnapshotcreationLogScanOtherLog_reuse_wait_desc在sys.datab縮小交易紀(jì)錄檔縮小交易紀(jì)錄檔案大小使用DBCCSHRINKFILE確定縮小交易紀(jì)錄檔後，再擴(kuò)增到適當(dāng)大小縮小只能到某個(gè)VLF的邊界縮小交易紀(jì)錄檔縮小交易紀(jì)錄檔案大小DemoVLF過多與破碎DemoVLF過多與破碎Q&AQ&AmsdnResourcesforDevelopers/

Forums/zh-tw/categories/LearningMicrosoftCertif