你知道異動資料表transactionlog做了什麼嗎?

sql server可以透過fn_dblog來查看transaction log紀錄的內容，本次想針對insert語法到底記錄了什麼資訊。

1.先建立一個資料表

CREATE TABLE TestDBLog (COL1 VARCHAR (30),COL2 VARCHAR(20))

–查看建立資料表的log
SELECT * FROM fn_dblog(null,null)  Where [transaction Name]=’CREATE TABLE’

–將以上的[Transaction ID]帶入查看建立資料表所有的動作

SELECT * FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:0000039a’)
order by [Transaction ID]

由以上得知建立一個資料表在transaction log寫入的24筆資料

–查看針對哪一個資料表進行建立
SELECT [Lock Information],* FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:0000039a’)
and [Lock Information] like ‘%SCH_M OBJECT%’

SELECT db_name(16),OBJECT_name(‘565577053‘)

從[Lock Information]欄位，可以看出16代表是資料庫的id,565577053代表是資料表id

2.使用insert into 新增 兩筆紀錄
INSERT INTO TestDBLog VALUES (‘TEST1′,’C1TEST1’)
INSERT INTO TestDBLog VALUES(‘TEST2′,’C2TEST2’)
SELECT * FROM TestDBLog

–查看新增資料的TranactionLog
SELECT * FROM fn_dblog(null,null)  Where Operation=’LOP_INSERT_ROWS’ AND AllocUnitName=’dbo.TestDBLog’

–將以上的[Transaction ID]帶入查看insert into所有的動作

SELECT * FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:0000039d’,’0000:000003a0′)
order by [Transaction ID]

由以上得知每一筆insert into 語法都會寫入三筆紀錄，每筆都做transaction

1. 第一筆是LOP_BEGIN_XACT是交易開始
2. 第二筆是LOP_INSERT_ROWS是新增資料
3. 第三筆是LOP_COMMIT_XACT COMMIT資料

以上得知若有100萬筆資料就會寫入300萬紀錄

3.使用交易insert into 新增 兩筆紀錄
begin tran
INSERT INTO TestDBLog VALUES (‘TEST3′,’C3TEST3’)
INSERT INTO TestDBLog VALUES(‘TEST4′,’C4TEST3’)

commit
SELECT * FROM TestDBLog

–查看新資料的TranactionLog
SELECT * FROM fn_dblog(null,null)  Where Operation=’LOP_INSERT_ROWS’ AND AllocUnitName=’dbo.TestDBLog’

這次新增的兩筆都是一樣的[Transaction ID]=0000:000003a1

–將以上的[Transaction ID]帶入查看INSERT資料表所有的動作
SELECT * FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:000003a1’)
order by [Transaction ID]

以上可看出若使用begin tran，所以被包在transaction內的資料只會做一次transaction及commit可以加快寫入速度

4.使用 insert into 多筆資料或select * from table 新增四筆紀錄
INSERT INTO TestDBLog  VALUES (‘TEST11′,’C1TEST11’),(‘TEST12′,’C2TEST12’) ,(‘TEST13′,’C3TEST13’) ,(‘TEST14′,’C4TEST14’)
SELECT * FROM TestDBLog

這次新增的兩筆都是一樣的[Transaction ID]=0000:000003a2

–將以上的[Transaction ID]帶入查看INSERT資料表所有的動作
SELECT * FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:000003a2’)
order by [Transaction ID]

以上可看出若使用整批insert也只會做一次transaction及commit可以加快寫入速度

5.使用交易將資料truncate table 後insert into 新增 兩筆紀錄在rollback檢查truncate 是否可以被rollback
begin tran
truncate table TestDBLog
INSERT INTO TestDBLog VALUES (‘TEST5′,’C5TEST5’)
INSERT INTO TestDBLog VALUES(‘TEST6′,’C6TEST6’)
rollback

SELECT * FROM TestDBLog

以上結果沒有看到新增的資料且原本的資料也沒有被刪除，表示truncate table可以被rollback

–查看新資料的TranactionLog
SELECT * FROM fn_dblog(null,null)  Where Operation=’LOP_INSERT_ROWS’ AND AllocUnitName=’dbo.TestDBLog’

這次新增的兩筆都是一樣的[Transaction ID]=0000:000003a3

–將以上的[Transaction ID]帶入查看truncate table 之後INSERT資料表所有的動作

SELECT * FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:000003a3’)
order by [Transaction ID]

從以上結果可看出truncate語法系統也有紀錄在transaction log且會進行資料的rollback動作，其中有兩筆是這次新增的紀錄與rollback的刪除紀錄。

6.使用交易將資料truncate table 後insert into 新增三筆紀錄在commit
begin tran
truncate table TestDBLog
INSERT INTO TestDBLog VALUES (‘TEST5′,’C5TEST5’)
INSERT INTO TestDBLog VALUES(‘TEST6′,’C6TEST6’)
INSERT INTO TestDBLog VALUES(‘TEST7′,’C6TEST7’)
commit

SELECT * FROM TestDBLog

–查看新資料的TranactionLog
SELECT * FROM fn_dblog(null,null)  Where Operation=’LOP_INSERT_ROWS’ AND AllocUnitName=’dbo.TestDBLog’

這次新增的兩筆都是一樣的[Transaction ID]=0000:000003a6

–將以上的[Transaction ID]帶入查看truncate table 之後INSERT資料表所有的動作
SELECT * FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:000003a6’)
order by [Transaction ID]

從以上結果可看出truncate語法之後commit紀錄的資料比較少

7.使用更新兩筆紀錄
update TestDBLog
set COL2=’this is update 5′
where COL1=’TEST5′
update TestDBLog
set COL2=’this is update 6′
where COL1=’TEST6′
SELECT * FROM TestDBLog

–查看更新資料的TranactionLog
SELECT * FROM fn_dblog(null,null)  Where Operation=’LOP_MODIFY_ROW’ and context=’LCX_HEAP’  AND AllocUnitName=’dbo.TestDBLog’

這次更新的[Transaction ID]=0000:000003ab,0000:000003ac

–將以上的[Transaction ID]帶入查看truncate table 之後INSERT資料表所有的動作
SELECT * FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:000003ab’,’0000:000003ac’)
order by [Transaction ID]

從以上結果可看出每更新一筆就產生三筆紀錄，每筆都做transaction

8.使用刪除兩筆紀錄
delete from  TestDBLog
where COL1 in(‘TEST5′,’TEST7’)
SELECT * FROM TestDBLog

–查看刪除資料的TranactionLog
SELECT * FROM fn_dblog(null,null)  Where Operation=’LOP_DELETE_ROWS’  and context=’LCX_HEAP’  AND AllocUnitName=’dbo.TestDBLog’

這次刪除兩筆資料都是相同的[Transaction ID]=0000:000003ad

–將以上的[Transaction ID]帶入查看刪除之後資料表所有的動作
SELECT * FROM fn_dblog(null,null)  Where [Transaction ID] in(‘0000:000003ad’)
order by [Transaction ID]