驗證未發生 Checkpoint 前,交易結果僅在 Log 和 Memory,未在資料庫檔

中午與俊宇聊天,他提及設定 recovery interval 來減少 I/O,讓我興起驗證是否這些動作是否僅有 .ldf 和記憶體有,但資料庫檔沒有,這麼做可以減少資料庫檔的 I/O,因為 .ldf 的 I/O 是免不了的。

換句話說,可能對同一筆紀錄發生的十次修改,這在 ldf 有十次紀錄。但因為延長 recovery interval,造成只做了一次 checkpoint,把十次對 memory data page 的最終結果,僅對 mdf 更新一次。

結果證明俊宇說的應該可行

–增長 CheckPoint 的週期

sp_configure ‘recovery interval (min)’,30
reconfigure

–簡單建點資料

create database dbTest
go
use dbTest
create table tb(c1 int identity, c2 char(100) default(‘Hello’),c3 uniqueidentifier default(newsequentialid()))
CHECKPOINT;

insert tb default values
select * from tb

begin tran
insert tb default values
exec sp_lock @@spid

image

commit
dbcc traceon(3604)
dbcc page(dbTest,1,80,1)

執行結果:

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息,請連絡您的系統管理員。

PAGE: (1:80)

BUFFER:

BUF @0x0000000086FABE80

bpage = 0x00000000861FC000           bhash = 0x0000000000000000           bpageno = (1:80)
bdbid = 28                           breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 48270                        bstat = 0xc0000b
blog = 0xca2159bb                    bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000861FC000

m_pageId = (1:80)                    m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904                                 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 124                        m_slotCnt = 2                        m_freeCnt = 7838
m_freeData = 350                     m_reservedCnt = 0                    m_lsn = (36:144:3)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 127, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 127

Memory Dump @0x0000000014B9A060

0000000000000000:   10007c00 01000000 48656c6c 6f202020 †..|…..Hello   
0000000000000010:   20202020 20202020 20202020 20202020 †
0000000000000020:   20202020 20202020 20202020 20202020 †
0000000000000030:   20202020 20202020 20202020 20202020 †
0000000000000040:   20202020 20202020 20202020 20202020 †
0000000000000050:   20202020 20202020 20202020 20202020 †
0000000000000060:   20202020 20202020 20202020 0b9dc040 †            …@
0000000000000070:   7a4211e0 a505705a b6d3467d 030000††††zB….pZ..F}…

Slot 1, Offset 0xdf, Length 127, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 127

Memory Dump @0x0000000014B9A0DF

0000000000000000:   10007c00 02000000 48656c6c 6f202020 †..|…..Hello
0000000000000010:   20202020 20202020 20202020 20202020 †
0000000000000020:   20202020 20202020 20202020 20202020 †
0000000000000030:   20202020 20202020 20202020 20202020 †
0000000000000040:   20202020 20202020 20202020 20202020 †
0000000000000050:   20202020 20202020 20202020 20202020 †
0000000000000060:   20202020 20202020 20202020 23f4a126 †            #..&
0000000000000070:   7a4211e0 a505705a b6d3467d 030000††††zB….pZ..F}…

OFFSET TABLE:

Row – Offset
1 (0x1) – 223 (0xdf)
0 (0x0) – 96 (0x60)

 

證明 Memory 資料都有上去

dbcc log(dbTest,3)

在 RowLog Contents 0 欄位的確看得到兩筆紀錄

立刻關掉 SQL Server,不准讓 Log 的內容更新到 mdf

SHUTDOWN WITH NOWAIT;

透過檔案總管刪掉 .ldf 後,重新啟動。透過以下語法救回 DB,等於是重建 ldf,但先前的交易內容就回不來了

alter database dbTest set emergency
alter database dbTest set single_user
DBCC CHECKDB (dbTest, REPAIR_ALLOW_DATA_LOSS)WITH  ALL_ERRORMSGS;

執行結果:

檔案啟用錯誤。實體檔案名稱 “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbTest_log.LDF" 可能不正確。
無法重建記錄,因為關閉資料庫時仍有開啟的交易/使用者、資料庫未發生檢查點,或資料庫是唯讀的。如果手動刪除交易記錄檔,或因硬體或環境失敗而遺失交易記錄檔,就可能發生這種錯誤。
警告: 資料庫 ‘dbTest’ 的記錄已經重建。已遺失交易一致性。RESTORE 鏈已中斷,伺服器已經沒有先前記錄檔的內容,因此您必須知道這些內容。您應該執行 DBCC CHECKDB 來驗證實體一致性。資料庫已進入僅限 DBO 模式。您準備好讓資料庫能供使用後,必須重設資料庫選項,並刪除任何額外的記錄檔。

讓資料庫回復正常:

alter database dbTest set multi_user
use dbTest
select * from tb

果然那些資料都沒有。

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: