程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SyBase數據庫 >> SyBase綜合文章 >> SQL Server 事務日志

SQL Server 事務日志

編輯:SyBase綜合文章

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空間大小依賴於用戶是如何使用數據庫的。

  例如:

  1. update,insert和delete 的頻率
  2. 每個transaction 中數據的修改量
  3. SQL Server系統參數recovery interval 值
  4. 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存放在不同的數據庫設備上。這樣做的好處:

  1. 可以單獨地備份(back up)transaction log
  2. 防止數據庫溢滿
  3. 可以看到log空間的使用情況。[dbcc checktable (syslogs)]
  4. 可以鏡像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增長速度十分快,如:

  1. 大量數據修改
  2. 刪除一個表的所有記錄
  3. 基於子查詢的數據插入
  4. 批量數據拷貝

  下面講述怎樣使用這些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.

  例如:


您正在看的Sybase教程是:SQL Server 事務日志。

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>go

bcp... -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的管理是靈活而復雜的,我們應該在實踐中摸索經驗,針對每個數據庫的不同情況,不同操作,做不同處理。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved