Sybase SQL Server日志問題普及
Sybase 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空間大小依賴于用戶是如何使用數據庫的。
例如：
◆update,insert和delete 的頻率
◆每個transaction 中數據的修改量
◆SQL Server系統參數recovery interval 值
◆log是否存到介質上用于數據庫恢復
還有其它因素影響log大小，我們應該根據操作估計log大小，并間隔一個周期就對log進行 備份和清除。
#p#
四、檢測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存放在不同的數據庫設備上。這樣做的好處：
◆可以單獨地備份(back up)transaction log
◆防止數據庫溢滿
◆可以看到log空間的使用情況。[dbcc checktable (syslogs)]
◆可以鏡像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。
#p#
七、管理大的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>;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。
#p#
八、Threshold 和transaction log 管理
SQL Server提供閾值管理功能，它能幫助用戶自動監視數據庫log設備段的自由空間。
在使用Sybase數據庫管理系統（SQL Server）開發企業應用系統時，或者開發好的數據庫 應用系統投入實際運行后，由于用戶不斷地增加或者修改數據庫中的數據，用戶數據庫的自由 存儲空間會日益減少。特別是數據庫日志，增長速度很快。一旦自由空間用盡，SQL Server在 缺省情況下掛起所有數據操縱事務，客戶端應用程序停止執行。這樣有可能會影響企業日常業 務處理流程。Sybase SQL Server System10提供自動監視數據庫自由存貯空間的機制——閾值 管理，當數據庫使用剩余空間低于一定值時，通過執行一個自定義的存儲過程，來控制自由空 間。在空間用完之前，采取相應措施，這樣有利于業務處理順利進行。如果能充分利用SQL Server的閾值管理功能，用戶能使一些數據庫管理工作自動化，規程化。所以，在此我們將 SQL Server這一重要功能介紹給讀者。
SQL Server的閾值管理允許用戶為數據庫的某個段上的自由空間設置閾值和定義相應的存 儲過程。當該段上的自由空間低于所置閾值時，SQL Server自動運行相應存儲過程。與閾值相 對應的存儲過程由用戶定義，SQL Server不提供。一般來說，數據庫管理員可通過這些存儲過 程來完成一些日常管理事務，例如：
◆備份數據庫，清理日志和刪除舊數據
◆備份數據庫日志
◆擴展數據庫空間
◆拷貝出表中的內容，清理日志，等等。
1.段（Segment）
SQL Server的閾值管理是基于段（Segment）的，因此，讓我們先回顧一下段的概念。每個 數據庫創建時，它有三個缺值段：（1）System段；（2）default段；（3） logsegment段。 以后，還可以為該數據庫增加用戶自定義段。將來所有的數據庫對象都建立在這些段上，要么 是系統定義的段，要么是用戶定義的段。數據庫的系統表存放在System段上。在沒有指明段時 ，建立的對象存放在default段上。數據庫的事務日志放在logsegment段上，該段是通過使用 建立數據庫（ Create database ）命令的log on 選項來定義的。
2.***機會閾值（Last_chance Threshold）
缺省情況下，SQLServer監測日志段的自由空間，當自由空間量低于事務日志能成功轉儲的 需要時，SQL Server運行sp_ thresholdaction過程。此自由空間量稱為***機會閾值（ Last_chance threshold ），它由SQL Server計算得來，并且用戶不能改變。
sp_thresholdaction必須由用戶編寫，SQL Server不提供。另外，如果***機會閾值越出 ，那么在日志空間釋放前，SQL Server一直掛起所有事務。但可以使用sp_dboption對某一數 據庫來改變這一行為。設置abort tran on log full選項為true，可使得***機會越出時， SQL Server撤回所有還未被注冊的事務。
3.閾值管理
系統缺省建立***機會閾值，由用戶編寫缺省閾值處理存儲過程（ sp_thresholdaction ），來控制自由空間。除此之外，還可以使用以下存儲過程管理閾值：
◆sp_addthreshold 建立一個閾值
◆sp_dropthreshold 刪除一個閾值
◆sp_helpthreshold 顯示閾值有關的信息
◆sp_dboption 改變閾值的“掛起或取消”行為和取消閾值管理
◆sp_helpsegment 顯示某個段上空間大小和自由空間大小的信息
#p#
4.增加閾值（sp_addthreshold）
它用于創建閾值（ threshold ）來監測數據庫段中空間的使用。如果段中自由空間低于指 定值，SQL Server運行有關的存儲過程。增加閾值的命令語法為：
sp_addthreshold database, segment, free_pages, procedure
其中：
◆database——要添加閾值的數據庫名。必須是當前數據庫名稱。
◆segment——其自由空間被監測的段。當指定“default“ 段時要用引號。
◆free_pages——閾值所指的自由空間頁數。當段中自由空間低于該標準時，SQL Server 運行有關存儲過程。
◆procedure——當segment中的自由空間低于free_pages時，SQL server 執行該存儲過程 。該過程可以放置在當前SQL Server或Open server的任意數據庫中。但是，超出閾值時，不 能執行遠程SQL Server上的存儲過程。
例如：sp_addthreshold pubs2, logsegment, 200, dump_transaction
其中，存儲過程定義為：
create procedure dump_transaction @ dbname varchar (30), @ segmentname varchar (30), @ space_left int, @ status int as dump transaction @dbname to "/dev/rmtx" |
那么，當日志段上可用空間小于200頁時，SQL Server運行存儲過程dump_transaction,將 pubs2 數據庫的日志轉儲到另一臺設置上。
sp_addthreshold不檢查存儲過程存在已否。但當閾值越出時，如果存儲過程不存在，SQL Server把錯誤信息送到錯誤日志（ errorlog ）中。系統允許每個數據庫有256個閾值，而同 一段上二個閾值之間的最小空間為128頁。其存儲過程可以是系統存儲過程，也可是在其它數 據庫里的存儲過程，或者Open Server遠過程調用。
5.刪除閾值（sp_dropthreshold）
它刪除某個段的自由空間閾值，但是不能刪除日志段的***機會閾植。刪除閾值的命令語 法為：
sp_dropthreshold database_name,segment_name,free_pages
其中三個參數分別為：閾值所屬數據庫名，閾值所監測的自由空間的段名，和自由空間頁 。例如：
sp_dropthreshold pubs2, logsegment, 200 （刪除 pubs2庫中logsegment段的閾值200）
6.顯示閾值（sp_helpthreshold）
它報告當前數據庫上與所有閾值有關的段，自由空間值，狀態以及存儲過程或報告某一特 定段的所有閾值。顯示閾值的語法為：
sp_helpthreshold [segment_name]
其中segment_name是當前數據庫上一個段的名字。
例如：
◆sp_helpthreshold logsegment 顯示在日志段上的所有閾值
◆sp_helpthreshold 顯示當前數據庫上所有段上的全部閾值
7.sp_dboption的新選擇
abort xact when log is full
當日志段的***機會閾值被超越時，試圖往該日志段上記日志的用戶進程將被掛起還是被 撤回？缺省情況下系統掛起所有進程。但是可以使用sp_aboption改變它。執行sp_dboption salesdb,＂abort xact when log is full＂,true 命令后，一旦日志滿了，則數據庫修改事 務將會被回滾。
disable free space acctg
這個選擇取消數據庫中的閾值管理，但不影響***機會閾值。執行：sp_dboption saledb, ＂disable free space acctg＂,true 它取消對非日志段上自由空間的統計。取消后，對系統 有以下影響：
◆SQL Server僅計算日志段上的自由空間
◆日志段上的閾值繼續處于活動狀態
◆在數據段上，系統表不改變，并且sp_spaceused得到的值是該選擇被取消時刻的值
◆數據庫段上的閾值失效
◆恢復加快
8.閾值的觸發過程
頻繁的插入和刪除可能會使數據庫段中的空間波動，閾值可能被多次超越，SQL Server使 用系統變量@@thresh_hysteresis，避免連續觸發閾值存儲過程。它的值由SQL Server設定。 在system 10.0中，@@thresh_hysteresis是64頁。
因此，增加一個閾值，它必須與下一個最近的閾值相距至少2*@@thresh_hysteresis頁。
一個閾值被觸發，需要以下幾個條件成立：
◆必須到達閾值
◆閾值處于活動狀態（即它被建立后或者自由空間達到閾值減于@@thresh_hysteresis）
◆只有自由空間減少閾值才觸發，如果自由空間增加，它永遠不觸發
【編輯推薦】
