Sybase SQL Server 的每一個(gè)數(shù)據(jù)庫，無論是系統(tǒng)數(shù)據(jù)庫（master,model, sybsystemprocs, tempdb），還是用戶數(shù)據(jù)庫，都有自己的transaction log，每個(gè)庫都有 syslogs表。Log記錄用戶對(duì)數(shù)據(jù)庫修改的操作，所以如果不用命令清除， log會(huì)一直增長直至 占滿空間。清除log可用dump transaction 命令；或者開放數(shù)據(jù)庫選項(xiàng)trunc log on chkpt， 數(shù)據(jù)庫會(huì)每隔一段間隔自動(dòng)清除log。管理好數(shù)據(jù)庫log是用戶操作數(shù)據(jù)庫必須考慮的一面。

下面就幾個(gè)方面談?wù)刲og及其管理：

一、SQL Server 如何記錄及讀取日志信息

我們知道，SQL Server是先記log的機(jī)制。Server Cache Memory中日志頁總是先寫于數(shù)據(jù)頁：

Log pages 在commit ,checkpoint,space needed 時(shí)寫入硬盤。

Data pages 在checkpoint,space needed 時(shí)寫入硬盤。

系統(tǒng)在recovery 時(shí)讀每個(gè)database 的syslogs 表的信息，回退未完成的事務(wù) (transaction)（數(shù)據(jù)改變到事務(wù)前狀態(tài)）；完成已提交的事務(wù)(transaction)（數(shù)據(jù)改變?yōu)槭?務(wù)提交后的狀態(tài)）。在Log中記下checkpoint點(diǎn)。這樣保證整個(gè)數(shù)據(jù)庫系統(tǒng)的一致性和完整性。

二、Transaction logs 和checkpoint 進(jìn)程

checkpoint 命令的功能是強(qiáng)制所有“臟”頁（自上次寫入數(shù)據(jù)庫設(shè)備后被更新過的頁）寫 入數(shù)據(jù)庫設(shè)備。自動(dòng)的checkpoint 間隔是由SQL Server 根據(jù)系統(tǒng)活動(dòng)和系統(tǒng)表 sysconfigures中的恢復(fù)間隔（recovery interval）值計(jì)算出的。通過指定系統(tǒng)恢復(fù)所需的時(shí) 間總量，恢復(fù)間隔決定了checkpoint 的頻率。

如果數(shù)據(jù)庫開放trunc log on chkpt選項(xiàng)，則SQL Server在數(shù)據(jù)庫系統(tǒng)執(zhí)行checkpoint時(shí) 自動(dòng)清除log。但用戶自己寫入執(zhí)行的checkpoint命令并不清除log，即使trunc log on chkpt 選項(xiàng)開放。只有在trunc log on chkpt選項(xiàng)開放時(shí)，SQL Server自動(dòng)執(zhí)行checkpoint動(dòng)作，才 能自動(dòng)清除log 。這個(gè)自動(dòng)的checkpoint動(dòng)作在SQL Server中的進(jìn)程叫做checkpoint進(jìn)程。當(dāng) trunc log on chkpt選項(xiàng)開放時(shí)，checkpoint進(jìn)程每隔0秒左右清除log，而不考慮recovery interval設(shè)置時(shí)間的間隔。

三、Transaction log 的大小

沒有一個(gè)十分嚴(yán)格的和確切的方法來確定一個(gè)數(shù)據(jù)庫的log應(yīng)該給多大空間。對(duì)一個(gè)新建的 數(shù)據(jù)庫來說，log大小為整個(gè)數(shù)據(jù)庫大小的20%左右。因?yàn)閘og記錄對(duì)數(shù)據(jù)庫的修改，如果修改 的動(dòng)作頻繁，則log的增長十分迅速。所以說log空間大小依賴于用戶是如何使用數(shù)據(jù)庫的。

例如：

◆update,insert和delete 的頻率

◆每個(gè)transaction 中數(shù)據(jù)的修改量

◆SQL Server系統(tǒng)參數(shù)recovery interval 值

◆log是否存到介質(zhì)上用于數(shù)據(jù)庫恢復(fù)

還有其它因素影響log大小，我們應(yīng)該根據(jù)操作估計(jì)log大小，并間隔一個(gè)周期就對(duì)log進(jìn)行 備份和清除。

#p#

四、檢測log 的大小

若log 在自己的設(shè)備上，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%

根據(jù)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 設(shè)備

一般來說，應(yīng)該將一個(gè)數(shù)據(jù)庫的data和log存放在不同的數(shù)據(jù)庫設(shè)備上。這樣做的好處：

◆可以單獨(dú)地備份(back up)transaction log 

◆防止數(shù)據(jù)庫溢滿

◆可以看到log空間的使用情況。[dbcc checktable (syslogs)] 

◆可以鏡像log設(shè)備

六、log 的清除

數(shù)據(jù)庫的log是不斷增長的，必須在它占滿空間之前清除。前面已經(jīng)討論過，清除log可以 開放數(shù)據(jù)庫選項(xiàng)trunc log on chkpt，使數(shù)據(jù)庫系統(tǒng)每隔一段時(shí)間間隔自動(dòng)清除log，還可以 執(zhí)行命令dump transaction 來清除log.trunc log on chkpt 選項(xiàng)同dump transaction with truncate_only 命令一樣，只是清除log而不保留log到備份設(shè)備上。所以如果只想清除log而 不做備份，可以使用trunc log on chkpt 選項(xiàng)及dump transaction with truncate_only,dump transaction with no_log 命令。若想備份，應(yīng)做dump transaction database_name to dumpdevice。

#p#

七、管理大的transactions

有些操作是大批量地修改數(shù)據(jù)，log增長速度十分快，如：

◆大量數(shù)據(jù)修改

◆刪除一個(gè)表的所有記錄

◆基于子查詢的數(shù)據(jù)插入

◆批量數(shù)據(jù)拷貝

下面講述怎樣使用這些transaction 使log 不至溢滿：

大量數(shù)據(jù)修改

例：

1>;update large_tab set col_1=0 
2>;go 

若這個(gè)表很大，則此update動(dòng)作在未完成之前就可能使log滿，引起1105錯(cuò)誤（log full）而且執(zhí)行這種大的transaction所產(chǎn)生的exclusive table loc，阻止其他用戶在update期間修 改這個(gè)表，這可能引起死鎖。為避免這些情況，我們可以把這個(gè)大的transaction分成幾個(gè)小 的transactions，并執(zhí)行dump transaction 動(dòng)作。

上述例子可以分成兩個(gè)或多個(gè)小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  

若這個(gè)transaction 需要備份到介質(zhì)上，則不用with truncate_only 選項(xiàng)。若執(zhí)行dump transaction with truncate_only，應(yīng)該先做dump database 命令。

刪除一個(gè)表的所有記錄

例：

1>;delete table large_tab 
2>;go  

同樣，把整個(gè)table的記錄都刪除，要記很多l(xiāng)og，我們可以用truncate table命令代替上 述語句完成相同功能。

1>;truncate table large_tab 
2>;go  

這樣，表中記錄都刪除了，而使用truncate table 命令，log只記錄空間回收情況，而不 是記錄刪除表中每一行的操作。

基于子查詢的數(shù)據(jù)插入

例：

1>;insert new_tab select col1,col2 from 

large_tab 
2>;go  

同樣的方法，對(duì)這個(gè)大的transaction，我們應(yīng)該處理為幾個(gè)小的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到介質(zhì)上，則dump transaction 后不加with truncate_only 選項(xiàng)。若 執(zhí)行dump transaction with truncate_only，應(yīng)該先做dump database 動(dòng)作。

批量數(shù)據(jù)拷貝

在使用bcp把數(shù)據(jù)拷入數(shù)據(jù)庫時(shí)，我們可以把這個(gè)大的transaction變成幾個(gè)小的 transactions處理，避免log劇增。

開放trunc log on chkpt 選項(xiàng)

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) 

關(guān)閉trunc log on chkpt選項(xiàng)，并dump database。

在這個(gè)例子中，一個(gè)批執(zhí)行100行拷貝。也可以將bcp輸入文件分成兩或多個(gè)分開的文件， 在每個(gè)文件執(zhí)行后做dump transaction 來避免log 滿。

若bcp使用快速方式（無索引，無triggers），這樣操作不記log，換句話說，log 只記載 空間分配情況。在這種情況下，要先做dump database（為恢復(fù)數(shù)據(jù)庫用）。若log太小，可置 trunc log on chkpt 選項(xiàng)，這樣在每次checkpoint后清除log。

#p#

八、Threshold 和transaction log 管理

SQL Server提供閾值管理功能，它能幫助用戶自動(dòng)監(jiān)視數(shù)據(jù)庫log設(shè)備段的自由空間。

在使用Sybase數(shù)據(jù)庫管理系統(tǒng)（SQL Server）開發(fā)企業(yè)應(yīng)用系統(tǒng)時(shí)，或者開發(fā)好的數(shù)據(jù)庫 應(yīng)用系統(tǒng)投入實(shí)際運(yùn)行后，由于用戶不斷地增加或者修改數(shù)據(jù)庫中的數(shù)據(jù)，用戶數(shù)據(jù)庫的自由 存儲(chǔ)空間會(huì)日益減少。特別是數(shù)據(jù)庫日志，增長速度很快。一旦自由空間用盡，SQL Server在 缺省情況下掛起所有數(shù)據(jù)操縱事務(wù)，客戶端應(yīng)用程序停止執(zhí)行。這樣有可能會(huì)影響企業(yè)日常業(yè) 務(wù)處理流程。Sybase SQL Server System10提供自動(dòng)監(jiān)視數(shù)據(jù)庫自由存貯空間的機(jī)制——閾值 管理，當(dāng)數(shù)據(jù)庫使用剩余空間低于一定值時(shí)，通過執(zhí)行一個(gè)自定義的存儲(chǔ)過程，來控制自由空 間。在空間用完之前，采取相應(yīng)措施，這樣有利于業(yè)務(wù)處理順利進(jìn)行。如果能充分利用SQL Server的閾值管理功能，用戶能使一些數(shù)據(jù)庫管理工作自動(dòng)化，規(guī)程化。所以，在此我們將 SQL Server這一重要功能介紹給讀者。

SQL Server的閾值管理允許用戶為數(shù)據(jù)庫的某個(gè)段上的自由空間設(shè)置閾值和定義相應(yīng)的存 儲(chǔ)過程。當(dāng)該段上的自由空間低于所置閾值時(shí)，SQL Server自動(dòng)運(yùn)行相應(yīng)存儲(chǔ)過程。與閾值相 對(duì)應(yīng)的存儲(chǔ)過程由用戶定義，SQL Server不提供。一般來說，數(shù)據(jù)庫管理員可通過這些存儲(chǔ)過 程來完成一些日常管理事務(wù)，例如：

◆備份數(shù)據(jù)庫，清理日志和刪除舊數(shù)據(jù)

◆備份數(shù)據(jù)庫日志

◆擴(kuò)展數(shù)據(jù)庫空間

◆拷貝出表中的內(nèi)容，清理日志，等等。

1.段（Segment）

SQL Server的閾值管理是基于段（Segment）的，因此，讓我們先回顧一下段的概念。每個(gè) 數(shù)據(jù)庫創(chuàng)建時(shí)，它有三個(gè)缺值段：（1）System段；（2）default段；（3） logsegment段。 以后，還可以為該數(shù)據(jù)庫增加用戶自定義段。將來所有的數(shù)據(jù)庫對(duì)象都建立在這些段上，要么 是系統(tǒng)定義的段，要么是用戶定義的段。數(shù)據(jù)庫的系統(tǒng)表存放在System段上。在沒有指明段時(shí) ，建立的對(duì)象存放在default段上。數(shù)據(jù)庫的事務(wù)日志放在logsegment段上，該段是通過使用 建立數(shù)據(jù)庫（ Create database ）命令的log on 選項(xiàng)來定義的。

2.***機(jī)會(huì)閾值（Last_chance Threshold）

缺省情況下，SQLServer監(jiān)測日志段的自由空間，當(dāng)自由空間量低于事務(wù)日志能成功轉(zhuǎn)儲(chǔ)的 需要時(shí)，SQL Server運(yùn)行sp_ thresholdaction過程。此自由空間量稱為***機(jī)會(huì)閾值（ Last_chance threshold ），它由SQL Server計(jì)算得來，并且用戶不能改變。

sp_thresholdaction必須由用戶編寫，SQL Server不提供。另外，如果***機(jī)會(huì)閾值越出 ，那么在日志空間釋放前，SQL Server一直掛起所有事務(wù)。但可以使用sp_dboption對(duì)某一數(shù) 據(jù)庫來改變這一行為。設(shè)置abort tran on log full選項(xiàng)為true，可使得***機(jī)會(huì)越出時(shí)， SQL Server撤回所有還未被注冊(cè)的事務(wù)。

3.閾值管理

系統(tǒng)缺省建立***機(jī)會(huì)閾值，由用戶編寫缺省閾值處理存儲(chǔ)過程（ sp_thresholdaction ），來控制自由空間。除此之外，還可以使用以下存儲(chǔ)過程管理閾值：

◆sp_addthreshold 建立一個(gè)閾值

◆sp_dropthreshold 刪除一個(gè)閾值

◆sp_helpthreshold 顯示閾值有關(guān)的信息

◆sp_dboption 改變閾值的“掛起或取消”行為和取消閾值管理

◆sp_helpsegment 顯示某個(gè)段上空間大小和自由空間大小的信息

#p#

4.增加閾值（sp_addthreshold）

它用于創(chuàng)建閾值（ threshold ）來監(jiān)測數(shù)據(jù)庫段中空間的使用。如果段中自由空間低于指 定值，SQL Server運(yùn)行有關(guān)的存儲(chǔ)過程。增加閾值的命令語法為：
sp_addthreshold database, segment, free_pages, procedure

其中：

◆database——要添加閾值的數(shù)據(jù)庫名。必須是當(dāng)前數(shù)據(jù)庫名稱。

◆segment——其自由空間被監(jiān)測的段。當(dāng)指定“default“ 段時(shí)要用引號(hào)。

◆free_pages——閾值所指的自由空間頁數(shù)。當(dāng)段中自由空間低于該標(biāo)準(zhǔn)時(shí)，SQL Server 運(yùn)行有關(guān)存儲(chǔ)過程。

◆procedure——當(dāng)segment中的自由空間低于free_pages時(shí)，SQL server 執(zhí)行該存儲(chǔ)過程 。該過程可以放置在當(dāng)前SQL Server或Open server的任意數(shù)據(jù)庫中。但是，超出閾值時(shí)，不 能執(zhí)行遠(yuǎn)程SQL Server上的存儲(chǔ)過程。

例如：sp_addthreshold pubs2, logsegment, 200, dump_transaction

其中，存儲(chǔ)過程定義為：

create procedure dump_transaction 
@ dbname varchar (30), 
@ segmentname varchar (30), 
@ space_left int, 
@ status int 
as 
dump transaction @dbname to "/dev/rmtx"  

那么，當(dāng)日志段上可用空間小于200頁時(shí)，SQL Server運(yùn)行存儲(chǔ)過程dump_transaction,將 pubs2 數(shù)據(jù)庫的日志轉(zhuǎn)儲(chǔ)到另一臺(tái)設(shè)置上。

sp_addthreshold不檢查存儲(chǔ)過程存在已否。但當(dāng)閾值越出時(shí)，如果存儲(chǔ)過程不存在，SQL Server把錯(cuò)誤信息送到錯(cuò)誤日志（ errorlog ）中。系統(tǒng)允許每個(gè)數(shù)據(jù)庫有256個(gè)閾值，而同 一段上二個(gè)閾值之間的最小空間為128頁。其存儲(chǔ)過程可以是系統(tǒng)存儲(chǔ)過程，也可是在其它數(shù) 據(jù)庫里的存儲(chǔ)過程，或者Open Server遠(yuǎn)過程調(diào)用。

5.刪除閾值（sp_dropthreshold）

它刪除某個(gè)段的自由空間閾值，但是不能刪除日志段的***機(jī)會(huì)閾植。刪除閾值的命令語 法為：

sp_dropthreshold database_name,segment_name,free_pages

其中三個(gè)參數(shù)分別為：閾值所屬數(shù)據(jù)庫名，閾值所監(jiān)測的自由空間的段名，和自由空間頁 。例如：

sp_dropthreshold pubs2, logsegment, 200     （刪除 pubs2庫中l(wèi)ogsegment段的閾值200）

6.顯示閾值（sp_helpthreshold）

它報(bào)告當(dāng)前數(shù)據(jù)庫上與所有閾值有關(guān)的段，自由空間值，狀態(tài)以及存儲(chǔ)過程或報(bào)告某一特 定段的所有閾值。顯示閾值的語法為：

sp_helpthreshold [segment_name]

其中segment_name是當(dāng)前數(shù)據(jù)庫上一個(gè)段的名字。

例如：

◆sp_helpthreshold logsegment 顯示在日志段上的所有閾值

◆sp_helpthreshold 顯示當(dāng)前數(shù)據(jù)庫上所有段上的全部閾值

7.sp_dboption的新選擇

abort xact when log is full

當(dāng)日志段的***機(jī)會(huì)閾值被超越時(shí)，試圖往該日志段上記日志的用戶進(jìn)程將被掛起還是被 撤回？缺省情況下系統(tǒng)掛起所有進(jìn)程。但是可以使用sp_aboption改變它。執(zhí)行sp_dboption salesdb,＂abort xact when log is full＂,true 命令后，一旦日志滿了，則數(shù)據(jù)庫修改事 務(wù)將會(huì)被回滾。

disable free space acctg

這個(gè)選擇取消數(shù)據(jù)庫中的閾值管理，但不影響***機(jī)會(huì)閾值。執(zhí)行：sp_dboption saledb, ＂disable free space acctg＂,true 它取消對(duì)非日志段上自由空間的統(tǒng)計(jì)。取消后，對(duì)系統(tǒng) 有以下影響：

◆SQL Server僅計(jì)算日志段上的自由空間

◆日志段上的閾值繼續(xù)處于活動(dòng)狀態(tài)

◆在數(shù)據(jù)段上，系統(tǒng)表不改變，并且sp_spaceused得到的值是該選擇被取消時(shí)刻的值

◆數(shù)據(jù)庫段上的閾值失效

◆恢復(fù)加快

8.閾值的觸發(fā)過程

頻繁的插入和刪除可能會(huì)使數(shù)據(jù)庫段中的空間波動(dòng)，閾值可能被多次超越，SQL Server使 用系統(tǒng)變量@@thresh_hysteresis，避免連續(xù)觸發(fā)閾值存儲(chǔ)過程。它的值由SQL Server設(shè)定。 在system 10.0中，@@thresh_hysteresis是64頁。

因此，增加一個(gè)閾值，它必須與下一個(gè)最近的閾值相距至少2*@@thresh_hysteresis頁。

一個(gè)閾值被觸發(fā)，需要以下幾個(gè)條件成立：

◆必須到達(dá)閾值

◆閾值處于活動(dòng)狀態(tài)（即它被建立后或者自由空間達(dá)到閾值減于@@thresh_hysteresis）

◆只有自由空間減少閾值才觸發(fā)，如果自由空間增加，它永遠(yuǎn)不觸發(fā)

【編輯推薦】

1. Sybase的操作型BI執(zhí)行手冊(cè)
2. Sybase Adaptive Server Enterprise 15的數(shù)據(jù)分區(qū)
3. 在Sybase Adaptive Server Enterprise中保護(hù)數(shù)據(jù)隱私
4. 如何設(shè)置Sybase的用戶權(quán)限來處理進(jìn)程