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

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

2、寫在更新資料檔之前,所有的變化須能永久記錄在交易紀(jì)錄內(nèi)Atomic變更能全部完成,或完全沒變交易紀(jì)錄能保證回復(fù)可行Durability變更能在穩(wěn)定的媒體上永久保存放置交易紀(jì)錄檔交易紀(jì)錄檔應(yīng)該放哪？專用的 LUN 通常是較好的選項(xiàng)如果存取量高,需要專屬的磁碟系統(tǒng)RAID 1 或 RAID 10 提供保護(hù)與效能RAID 5 通常不利於更新儘量不要與其他檔案共用不同的檔案放在一起存取,大家都變成隨機(jī)存取SQL Server 資料庫(kù)上線資料庫(kù)需要經(jīng)過(guò)一系列的動(dòng)作,才能進(jìn)入一致的狀態(tài)一般稱為 Recovery需要交易紀(jì)錄何時(shí)發(fā)生：資料庫(kù)從離線轉(zhuǎn)成線上附加資料庫(kù)還原資料庫(kù)服務(wù)停止重起/叢集容錯(cuò)移轉(zhuǎn)Reco

3、very 的階段Checkpoint確保 Recovery 的 Redo 階段最少工作量將更新過(guò)的資料頁(yè)(dirty page)寫入到磁碟內(nèi)的資料檔不管交易狀態(tài)如何,將所有的 dirty page 寫入到資料檔可能造成大量的 I/O不會(huì)從 Buffer pool 移除資料頁(yè)當(dāng)有記憶體壓力時(shí),Lazy Writer 執(zhí)行此工作Checkpoint 相關(guān)選項(xiàng)搭配 sp_configure 設(shè)定執(zhí)行個(gè)體recovery interval 選項(xiàng)SQL Server 2012 後可設(shè)定資料庫(kù)層級(jí) Target_Recovery_Time 選項(xiàng)DemoCheckpoint交易紀(jì)錄保證資料庫(kù)的交易每筆紀(jì)錄透

4、過(guò) Log Sequence Number 唯一標(biāo)示不僅與完成的交易有關(guān)記載所有的變動(dòng)包含可以 Redo 和 Undo 交易的所有資訊大綱交易紀(jì)錄簡(jiǎn)介CheckpointVirtual Log Files 結(jié)構(gòu)VLF 重用VLF 破碎資料庫(kù)復(fù)原模式交易紀(jì)錄備份Bulk交易紀(jì)錄維護(hù)交易紀(jì)錄循環(huán)使用交易紀(jì)錄資料庫(kù)復(fù)原模式交易紀(jì)錄備份將 Redo 和 Undo 不再需要的交易紀(jì)錄存放到備份檔中,並從交易紀(jì)錄檔移除搭配完整備份,以還原資料庫(kù)到特定時(shí)間點(diǎn)Simple 復(fù)原模式無(wú)法備份交易紀(jì)錄不保證交易紀(jì)錄空間會(huì)被重用不會(huì)縮小交易紀(jì)錄檔案Bulk Logged特定的行為可以讓 SQL Server 僅記錄

5、發(fā)生該行為以及使用的空間可以讓動(dòng)作執(zhí)行得較快注意：在 Bulk Logged 動(dòng)作完成交易前,必須將受影響的資料頁(yè)內(nèi)容寫入資料檔交易紀(jì)錄檔成長(zhǎng)得較小,但交易紀(jì)錄備份檔不會(huì)較小DemoBulk Logged 行為大綱交易紀(jì)錄簡(jiǎn)介CheckpointVirtual Log Files 結(jié)構(gòu)VLF 重用VLF 破碎資料庫(kù)復(fù)原模式交易紀(jì)錄備份Bulk交易紀(jì)錄維護(hù)交易紀(jì)錄循環(huán)使用交易紀(jì)錄記錄的行為我們希望交易紀(jì)錄檔有限地成長(zhǎng)一旦不再需要局部的紀(jì)錄,期待該空間可以回收使用交易紀(jì)錄有循環(huán)的特徵,應(yīng)可以重用空間DBA 負(fù)有管好交易紀(jì)錄的責(zé)任什麼是合理的交易紀(jì)錄檔成長(zhǎng)量,異常成長(zhǎng)的原因?yàn)楹?？交易區(qū)塊(Log B

6、lock)實(shí)體寫入到交易紀(jì)錄檔的單位大小從 512 位元組到 60K在記憶體中以 “l(fā)og buffer 儲(chǔ)存儲(chǔ)存在 Buffer Pool交易完畢或?qū)憹M 60K,就更新到交易紀(jì)錄檔較大的交易量(接近 60K)完成寫入較有效率有時(shí)多筆小交易對(duì)效能不利虛擬交易紀(jì)錄檔(Virtual Log File)在交易紀(jì)錄檔內(nèi)去除交易的單位在建立交易紀(jì)錄檔或增長(zhǎng)檔案大小時(shí)產(chǎn)生資料庫(kù)內(nèi)有多少數(shù)量的 VLF 是有差異的交易紀(jì)錄檔增長(zhǎng)增加 VLF 的量 64 MB 和 1 GB16需要多少 VLF 檔案依需求！太多,傷讀取紀(jì)錄和還原資料庫(kù)的效能太少,很長(zhǎng)時(shí)間才能釋放和重用 VLF 區(qū)段,造成交易紀(jì)錄檔更多的成長(zhǎng)假

7、設(shè) VLF 檔案太多,SQL Server 會(huì)發(fā)出警告Database has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.Log Sequence Number資料庫(kù)內(nèi)狀態(tài)變化的識(shí)別子

8、每個(gè)交易紀(jì)錄的唯一識(shí)別編碼單純遞增VLF 編號(hào)：交易區(qū)塊編號(hào)：交易紀(jì)錄編號(hào)DemoVLF 格式與重用交易紀(jì)錄為何交易紀(jì)錄檔如此大很多朋友的經(jīng)驗(yàn)通常是 Full 復(fù)原模式,但不常做交易紀(jì)錄備份可能巨大交易造成的紀(jì)錄有時(shí)需要在其他的磁碟機(jī)加交易紀(jì)錄檔解法：修正其下的問(wèn)題縮小交易紀(jì)錄檔將Log檔增大到適當(dāng)?shù)拇笮≈赜?VLF重用 VLF 需要：在當(dāng)下或之前的 VLFs 沒有還在進(jìn)行中的交易沒有需要完成其他工作的交易紀(jì)錄,例如還未複寫資料庫(kù)復(fù)原模式為 Simple 時(shí),在 Checkpoint 後可重用資料庫(kù)復(fù)原模式為 Bulk Logged 或 Full 需要在備份交易紀(jì)錄後Log_reuse_wai

9、t_desc在 sys.databases 內(nèi)的欄位傳會(huì)當(dāng)下紀(jì)錄未重用的原因,確認(rèn)交易紀(jì)錄為何留著可能值如右表NothingCheckpointLog backupActive backup or restoreActive TransactionDatabase MirroringReplicationDatabase snapshot creationLog ScanOther縮小交易紀(jì)錄檔縮小交易紀(jì)錄檔案大小使用 DBCC SHRINKFILE確定縮小交易紀(jì)錄檔後,再擴(kuò)增到適當(dāng)大小縮小只能到某個(gè) VLF 的邊界DemoVLF 過(guò)多與破碎Q&AmsdnResources for Devel

10、opersForums/zh-tw/categories/LearningMicrosoft Certification & Training Resourceszh/tw TechNet相關(guān)資源Connect. Share. DiscussChannel9TaiwanResources for IT ProfessionalsForums/zh-tw/categories 填妥大會(huì)背包中的大會(huì)問(wèn)卷,可於活動(dòng)第三天兌換問(wèn)卷禮哦！ 請(qǐng)協(xié)助完成本課程問(wèn)卷,並在離開教室時(shí)交給工作人員！ 感謝您的合作。 2013 Microsoft Corporation. All rights reserved.

11、Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee th