SSybase SQL Server 的每一個數據庫,無論是系統數據庫(master,model, sybsystemprocs, tempdb),還是用戶數據庫,都有自己的transaction log,每個庫都有syslogs表。Log記錄用戶對數據庫修改的操作,所以如果不用命令清除, log會一直增長直至占滿空間。清除log可用dump transaction 命令;或者開放數據庫選項trunc log on chkpt,數據庫會每隔一段間隔自動清除log。管理好數據庫log是用戶操作數據庫必須考慮的一面。
下面就幾個方面談談log及其管理:
一、SQL Server 如何記錄及讀取日志信息
我們知道,SQL Server是先記log的機制。Server Cache Memory中日志頁總是先寫於數據頁
Log pages 在commit ,checkpoint,space needed 時寫入硬盤。
Data pages 在checkpoint,space needed 時寫入硬盤。
系統在recovery 時讀每個database 的syslogs 表的信息,回退未完成的事務(transaction)(數據改變到事務前狀態);完成已提交的事務(transaction)(數據改變為事務提交後的狀態)。在Log中記下checkpoint點。這樣保證整個數據庫系統的一致性和完整性。
二、Transaction logs 和checkpoint 進程
checkpoint 命令的功能是強制所有“髒”頁(自上次寫入數據庫設備後被更新過的頁)寫入數據庫設備。自動的checkpoint 間隔是由SQL Server 根據系統活動和系統表sysconfigures中的恢復間隔(recovery interval)值計算出的。通過指定系統恢復所需的時間總量,恢復間隔決定了checkpoint 的頻率。
如果數據庫開放trunc log on chkpt選項,則SQL Server在數據庫系統執行checkpoint時自動清除log。但用戶自己寫入執行的checkpoint命令並不清除log,即使trunc log on chkpt選項開放。只有在trunc log on chkpt選項開放時,SQL Server自動執行checkpoint動作,才能自動清除log 。這個自動的checkpoint動作在SQL Server中的進程叫做checkpoint進程。當trunc log on chkpt選項開放時,checkpoint進程每隔0秒左右清除log,而不考慮recovery interval設置時間的間隔。
三、Transaction log 的大小
沒有一個十分嚴格的和確切的方法來確定一個數據庫的log應該給多大空間。對一個新建的數據庫來說,log大小為整個數據庫大小的20%左右。因為log記錄對數據庫的修改,如果修改的動作頻繁,則log的增長十分迅速。所以說log空間大小依賴於用戶是如何使用數據庫的。
例如:
還有其它因素影響log大小,我們應該根據操作估計log大小,並間隔一個周期就對log進行備份和清除。
四、檢測log 的大小
若log 在自己的設備上,dbcc checktable (syslogs) 有如下信息:
例:***NOTICE:space used on the log segment is 12.87Mbytes,64.35%
***NOTICE:space free on the log segment is 7.13Mbytes,35.65%
根據log剩余空間比例來決定是否使用dump transaction 命令來備份和清除log。
用快速方法來判斷transaction log 滿的程度。
1>use database_name
2>go
1>select data_pgs (8,doa mpg)
2>from sysindexes where id=8
3>go
Note:this query may be off by as many as 16 pages.
在syslogs 表用sp_spaceused 命令。
五、log 設備
一般來說,應該將一個數據庫的data和log存放在不同的數據庫設備上。這樣做的好處:
六、log 的清除
數據庫的log是不斷增長的,必須在它占滿空間之前清除。前面已經討論過,清除log可以開放數據庫選項trunc log on chkpt,使數據庫系統每隔一段時間間隔自動清除log,還可以執行命令dump transaction 來清除log.trunc log on chkpt 選項同dump transaction with truncate_only 命令一樣,只是清除log而不保留log到備份設備上。所以如果只想清除log而不做備份,可以使用trunc log on chkpt 選項及dump transaction with truncate_only,dump transaction with no_log 命令。若想備份,應做dump transaction database_name to dumpdevice。
七、管理大的transactions
有些操作是大批量地修改數據,log增長速度十分快,如:
下面講述怎樣使用這些transaction 使log 不至溢滿:
大量數據修改
例 :
1>update large_tab set col_1=0
2>go
若這個表很大,則此update動作在未完成之前就可能使log滿,引起1105錯誤(log full)而且執行這種大的transaction所產生的exclusive table loc,阻止其他用戶在update期間修改這個表,這可能引起死鎖。為避免這些情況,我們可以把這個大的transaction分成幾個小的transactions,並執行dump transaction 動作。
上述例子可以分成兩個或多個小transactions.
例如:
1>update large_tab set col1=0
2>where col2 3>go
1>dump transaction database_name with truncate_only
2>go
1>update large_tab set col1=0
2>where col2>=x
3>go
1>dump transaction database_name with truncate_only
2>go
若這個transaction 需要備份到介質上,則不用with truncate_only 選項。若執 行dump transaction with truncate_only,應該先做dump database 命令。
刪除一個表的所有記錄:
例:
1>delete table large_tab
2>go
同樣,把整個table的記錄都刪除,要記很多log,我們可以用truncate table命令代替上述語句完成相同功能。
1>truncate table large_tab
2>go
這樣,表中記錄都刪除了,而使用truncate table 命令,log只記錄空間回收情況,而不是記錄刪除表中每一行的操作。
基於子查詢的數據插入
例:
1>insert new_tab select col1,col2 from large_tab
2>go
同樣的方法,對這個大的transaction,我們應該處理為幾個小的transactions。
1>Insert new_tab
2>select col1,col2 from large_tab where col1<=y
3>go
1>dump transaction database_name with truncate_only
2>go
1>insert new_tab
2>select col1,col2 from large_tab where col1>y
3>go
1>dump database database_name with truncate_only
2>go
同樣,若想保存log到介質上,則dump transaction 後不加with truncate_only 選項。若執行dump transaction with truncate_only,應該先做dump database 動作。
批量數據拷貝
在使用bcp把數據拷入數據庫時,我們可以把這個大的transaction變成幾個小的transactions處理,避免log劇增。
開放trunc log on chkpt 選項
1>use master
2>go
1>sp_dboption database_name,trunc,true
2>go
1>use database_name
2>go
1>checkpoint
2>gobcp... -b 100 (on unix)
bcp... /batch_size=100(on vms)
關閉trunc log on chkpt選項,並dump database。
在這個例子中,一個批執行100行拷貝。也可以將bcp輸入文件分成兩或多個分開的文件,在每個文件執行後做dump transaction 來避免log 滿。
若bcp使用快速方式(無索引,無triggers),這樣操作不記log,換句話說,log 只記載空間分配情況。在這種情況下,要先做dump database(為恢復數據庫用)。若log太小,可置trunc log on chkpt 選項,這樣在每次checkpoint後清除log。
八、Threshold 和transaction log 管理
SQL Server提供阈值管理功能,它能幫助用戶自動監視數據庫log設備段的自由空間。這方面的詳細討論見NO.5技術支持雜志。log的管理是靈活而復雜的,我們應該在實踐中摸索經驗,針對每個數據庫的不同情況,不同操作,做不同處理。