建站
咨詢
售后
建站咨詢
新聞中心
Sybase SQL Server 的每一個數(shù)據(jù)庫，無論是系統(tǒng)數(shù)據(jù)庫（master,model, sybsystemprocs, tempdb），還是用戶數(shù)據(jù)庫，都有自己的transaction log，每個庫都有 syslogs表。Log記錄用戶對數(shù)據(jù)庫修改的操作，所以如果不用命令清除， log會一直增長直至 占滿空間。清除log可用dump transaction 命令；或者開放數(shù)據(jù)庫選項trunc log on chkpt， 數(shù)據(jù)庫會每隔一段間隔自動清除log。管理好數(shù)據(jù)庫log是用戶操作數(shù)據(jù)庫必須考慮的一面。
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供和碩網(wǎng)站建設(shè)、和碩做網(wǎng)站、和碩網(wǎng)站設(shè)計、和碩網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計與制作、和碩企業(yè)網(wǎng)站模板建站服務(wù)，10多年和碩做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡(luò)服務(wù)。
下面就幾個方面談?wù)刲og及其管理：
一、SQL Server 如何記錄及讀取日志信息
我們知道，SQL Server是先記log的機(jī)制。Server Cache Memory中日志頁總是先寫于數(shù)據(jù)頁：
Log pages 在commit ,checkpoint,space needed 時寫入硬盤。
Data pages 在checkpoint,space needed 時寫入硬盤。
系統(tǒng)在recovery 時讀每個database 的syslogs 表的信息，回退未完成的事務(wù) (transaction)（數(shù)據(jù)改變到事務(wù)前狀態(tài)）；完成已提交的事務(wù)(transaction)（數(shù)據(jù)改變?yōu)槭?務(wù)提交后的狀態(tài)）。在Log中記下checkpoint點。這樣保證整個數(shù)據(jù)庫系統(tǒng)的一致性和完整性。
二、Transaction logs 和checkpoint 進(jìn)程
checkpoint 命令的功能是強(qiáng)制所有“臟”頁（自上次寫入數(shù)據(jù)庫設(shè)備后被更新過的頁）寫 入數(shù)據(jù)庫設(shè)備。自動的checkpoint 間隔是由SQL Server 根據(jù)系統(tǒng)活動和系統(tǒng)表 sysconfigures中的恢復(fù)間隔（recovery interval）值計算出的。通過指定系統(tǒng)恢復(fù)所需的時 間總量，恢復(fù)間隔決定了checkpoint 的頻率。
如果數(shù)據(jù)庫開放trunc log on chkpt選項，則SQL Server在數(shù)據(jù)庫系統(tǒng)執(zhí)行checkpoint時 自動清除log。但用戶自己寫入執(zhí)行的checkpoint命令并不清除log，即使trunc log on chkpt 選項開放。只有在trunc log on chkpt選項開放時，SQL Server自動執(zhí)行checkpoint動作，才 能自動清除log 。這個自動的checkpoint動作在SQL Server中的進(jìn)程叫做checkpoint進(jìn)程。當(dāng) trunc log on chkpt選項開放時，checkpoint進(jìn)程每隔0秒左右清除log，而不考慮recovery interval設(shè)置時間的間隔。
三、Transaction log 的大小
沒有一個十分嚴(yán)格的和確切的方法來確定一個數(shù)據(jù)庫的log應(yīng)該給多大空間。對一個新建的 數(shù)據(jù)庫來說，log大小為整個數(shù)據(jù)庫大小的20%左右。因為log記錄對數(shù)據(jù)庫的修改，如果修改 的動作頻繁，則log的增長十分迅速。所以說log空間大小依賴于用戶是如何使用數(shù)據(jù)庫的。
例如：
◆update,insert和delete 的頻率
◆每個transaction 中數(shù)據(jù)的修改量
◆SQL Server系統(tǒng)參數(shù)recovery interval 值
◆log是否存到介質(zhì)上用于數(shù)據(jù)庫恢復(fù)
還有其它因素影響log大小，我們應(yīng)該根據(jù)操作估計log大小，并間隔一個周期就對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)該將一個數(shù)據(jù)庫的data和log存放在不同的數(shù)據(jù)庫設(shè)備上。這樣做的好處：
◆可以單獨地備份(back up)transaction log
◆防止數(shù)據(jù)庫溢滿
◆可以看到log空間的使用情況。[dbcc checktable (syslogs)]
◆可以鏡像log設(shè)備
六、log 的清除
數(shù)據(jù)庫的log是不斷增長的，必須在它占滿空間之前清除。前面已經(jīng)討論過，清除log可以 開放數(shù)據(jù)庫選項trunc log on chkpt，使數(shù)據(jù)庫系統(tǒng)每隔一段時間間隔自動清除log，還可以 執(zhí)行命令dump transaction 來清除log.trunc log on chkpt 選項同dump transaction with truncate_only 命令一樣，只是清除log而不保留log到備份設(shè)備上。所以如果只想清除log而 不做備份，可以使用trunc log on chkpt 選項及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ù)修改
◆刪除一個表的所有記錄
◆基于子查詢的數(shù)據(jù)插入
◆批量數(shù)據(jù)拷貝
下面講述怎樣使用這些transaction 使log 不至溢滿：
大量數(shù)據(jù)修改
例：
1>;update large_tab set col_1=0 2>;go |
若這個表很大，則此update動作在未完成之前就可能使log滿，引起1105錯誤（log full）而且執(zhí)行這種大的transaction所產(chǎn)生的exclusive table loc，阻止其他用戶在update期間修 改這個表，這可能引起死鎖。為避免這些情況，我們可以把這個大的transaction分成幾個小 的transactions，并執(zhí)行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 需要備份到介質(zhì)上，則不用with truncate_only 選項。若執(zhí)行dump transaction with truncate_only，應(yīng)該先做dump database 命令。
刪除一個表的所有記錄
例：
1>;delete table large_tab 2>;go |
同樣，把整個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 |
同樣的方法，對這個大的transaction，我們應(yīng)該處理為幾個小的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 選項。若 執(zhí)行dump transaction with truncate_only，應(yīng)該先做dump database 動作。
批量數(shù)據(jù)拷貝
在使用bcp把數(shù)據(jù)拷入數(shù)據(jù)庫時，我們可以把這個大的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) |
關(guān)閉trunc log on chkpt選項，并dump database。
在這個例子中，一個批執(zhí)行100行拷貝。也可以將bcp輸入文件分成兩或多個分開的文件， 在每個文件執(zhí)行后做dump transaction 來避免log 滿。
若bcp使用快速方式（無索引，無triggers），這樣操作不記log，換句話說，log 只記載 空間分配情況。在這種情況下，要先做dump database（為恢復(fù)數(shù)據(jù)庫用）。若log太小，可置 trunc log on chkpt 選項，這樣在每次checkpoint后清除log。
#p#
八、Threshold 和transaction log 管理
SQL Server提供閾值管理功能，它能幫助用戶自動監(jiān)視數(shù)據(jù)庫log設(shè)備段的自由空間。
在使用Sybase數(shù)據(jù)庫管理系統(tǒng)（SQL Server）開發(fā)企業(yè)應(yīng)用系統(tǒng)時，或者開發(fā)好的數(shù)據(jù)庫 應(yīng)用系統(tǒng)投入實際運(yùn)行后，由于用戶不斷地增加或者修改數(shù)據(jù)庫中的數(shù)據(jù)，用戶數(shù)據(jù)庫的自由 存儲空間會日益減少。特別是數(shù)據(jù)庫日志，增長速度很快。一旦自由空間用盡，SQL Server在 缺省情況下掛起所有數(shù)據(jù)操縱事務(wù)，客戶端應(yīng)用程序停止執(zhí)行。這樣有可能會影響企業(yè)日常業(yè) 務(wù)處理流程。Sybase SQL Server System10提供自動監(jiān)視數(shù)據(jù)庫自由存貯空間的機(jī)制——閾值 管理，當(dāng)數(shù)據(jù)庫使用剩余空間低于一定值時，通過執(zhí)行一個自定義的存儲過程，來控制自由空 間。在空間用完之前，采取相應(yīng)措施，這樣有利于業(yè)務(wù)處理順利進(jìn)行。如果能充分利用SQL Server的閾值管理功能，用戶能使一些數(shù)據(jù)庫管理工作自動化，規(guī)程化。所以，在此我們將 SQL Server這一重要功能介紹給讀者。
SQL Server的閾值管理允許用戶為數(shù)據(jù)庫的某個段上的自由空間設(shè)置閾值和定義相應(yīng)的存 儲過程。當(dāng)該段上的自由空間低于所置閾值時，SQL Server自動運(yùn)行相應(yīng)存儲過程。與閾值相 對應(yīng)的存儲過程由用戶定義，SQL Server不提供。一般來說，數(shù)據(jù)庫管理員可通過這些存儲過 程來完成一些日常管理事務(wù)，例如：
◆備份數(shù)據(jù)庫，清理日志和刪除舊數(shù)據(jù)
◆備份數(shù)據(jù)庫日志
◆擴(kuò)展數(shù)據(jù)庫空間
◆拷貝出表中的內(nèi)容，清理日志，等等。
1.段（Segment）
SQL Server的閾值管理是基于段（Segment）的，因此，讓我們先回顧一下段的概念。每個 數(shù)據(jù)庫創(chuàng)建時，它有三個缺值段：（1）System段；（2）default段；（3） logsegment段。 以后，還可以為該數(shù)據(jù)庫增加用戶自定義段。將來所有的數(shù)據(jù)庫對象都建立在這些段上，要么 是系統(tǒng)定義的段，要么是用戶定義的段。數(shù)據(jù)庫的系統(tǒng)表存放在System段上。在沒有指明段時 ，建立的對象存放在default段上。數(shù)據(jù)庫的事務(wù)日志放在logsegment段上，該段是通過使用 建立數(shù)據(jù)庫（ Create database ）命令的log on 選項來定義的。
2.***機(jī)會閾值（Last_chance Threshold）
缺省情況下，SQLServer監(jiān)測日志段的自由空間，當(dāng)自由空間量低于事務(wù)日志能成功轉(zhuǎn)儲的 需要時，SQL Server運(yùn)行sp_ thresholdaction過程。此自由空間量稱為***機(jī)會閾值（ Last_chance threshold ），它由SQL Server計算得來，并且用戶不能改變。
sp_thresholdaction必須由用戶編寫，SQL Server不提供。另外，如果***機(jī)會閾值越出 ，那么在日志空間釋放前，SQL Server一直掛起所有事務(wù)。但可以使用sp_dboption對某一數(shù) 據(jù)庫來改變這一行為。設(shè)置abort tran on log full選項為true，可使得***機(jī)會越出時， SQL Server撤回所有還未被注冊的事務(wù)。
3.閾值管理
系統(tǒng)缺省建立***機(jī)會閾值，由用戶編寫缺省閾值處理存儲過程（ sp_thresholdaction ），來控制自由空間。除此之外，還可以使用以下存儲過程管理閾值：
◆sp_addthreshold 建立一個閾值
◆sp_dropthreshold 刪除一個閾值
◆sp_helpthreshold 顯示閾值有關(guān)的信息
◆sp_dboption 改變閾值的“掛起或取消”行為和取消閾值管理
◆sp_helpsegment 顯示某個段上空間大小和自由空間大小的信息
#p#
4.增加閾值（sp_addthreshold）
它用于創(chuàng)建閾值（ threshold ）來監(jiān)測數(shù)據(jù)庫段中空間的使用。如果段中自由空間低于指 定值，SQL Server運(yùn)行有關(guān)的存儲過程。增加閾值的命令語法為：
sp_addthreshold database, segment, free_pages, procedure
其中：
◆database——要添加閾值的數(shù)據(jù)庫名。必須是當(dāng)前數(shù)據(jù)庫名稱。
◆segment——其自由空間被監(jiān)測的段。當(dāng)指定“default“ 段時要用引號。
◆free_pages——閾值所指的自由空間頁數(shù)。當(dāng)段中自由空間低于該標(biāo)準(zhǔn)時，SQL Server 運(yùn)行有關(guān)存儲過程。
◆procedure——當(dāng)segment中的自由空間低于free_pages時，SQL server 執(zhí)行該存儲過程 。該過程可以放置在當(dāng)前SQL Server或Open server的任意數(shù)據(jù)庫中。但是，超出閾值時，不 能執(zhí)行遠(yuǎn)程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" |
那么，當(dāng)日志段上可用空間小于200頁時，SQL Server運(yùn)行存儲過程dump_transaction,將 pubs2 數(shù)據(jù)庫的日志轉(zhuǎn)儲到另一臺設(shè)置上。
sp_addthreshold不檢查存儲過程存在已否。但當(dāng)閾值越出時，如果存儲過程不存在，SQL Server把錯誤信息送到錯誤日志（ errorlog ）中。系統(tǒng)允許每個數(shù)據(jù)庫有256個閾值，而同 一段上二個閾值之間的最小空間為128頁。其存儲過程可以是系統(tǒng)存儲過程，也可是在其它數(shù) 據(jù)庫里的存儲過程，或者Open Server遠(yuǎn)過程調(diào)用。
5.刪除閾值（sp_dropthreshold）
它刪除某個段的自由空間閾值，但是不能刪除日志段的***機(jī)會閾植。刪除閾值的命令語 法為：
sp_dropthreshold database_name,segment_name,free_pages
其中三個參數(shù)分別為：閾值所屬數(shù)據(jù)庫名，閾值所監(jiān)測的自由空間的段名，和自由空間頁 。例如：
sp_dropthreshold pubs2, logsegment, 200 （刪除 pubs2庫中l(wèi)ogsegment段的閾值200）
6.顯示閾值（sp_helpthreshold）
它報告當(dāng)前數(shù)據(jù)庫上與所有閾值有關(guān)的段，自由空間值，狀態(tài)以及存儲過程或報告某一特 定段的所有閾值。顯示閾值的語法為：
sp_helpthreshold [segment_name]
其中segment_name是當(dāng)前數(shù)據(jù)庫上一個段的名字。
例如：
◆sp_helpthreshold logsegment 顯示在日志段上的所有閾值
◆sp_helpthreshold 顯示當(dāng)前數(shù)據(jù)庫上所有段上的全部閾值
7.sp_dboption的新選擇
abort xact when log is full
當(dāng)日志段的***機(jī)會閾值被超越時，試圖往該日志段上記日志的用戶進(jìn)程將被掛起還是被 撤回？缺省情況下系統(tǒng)掛起所有進(jìn)程。但是可以使用sp_aboption改變它。執(zhí)行sp_dboption salesdb,＂abort xact when log is full＂,true 命令后，一旦日志滿了，則數(shù)據(jù)庫修改事 務(wù)將會被回滾。
disable free space acctg
這個選擇取消數(shù)據(jù)庫中的閾值管理，但不影響***機(jī)會閾值。執(zhí)行：sp_dboption saledb, ＂disable free space acctg＂,true 它取消對非日志段上自由空間的統(tǒng)計。取消后，對系統(tǒng) 有以下影響：
◆SQL Server僅計算日志段上的自由空間
◆日志段上的閾值繼續(xù)處于活動狀態(tài)
◆在數(shù)據(jù)段上，系統(tǒng)表不改變，并且sp_spaceused得到的值是該選擇被取消時刻的值
◆數(shù)據(jù)庫段上的閾值失效
◆恢復(fù)加快
8.閾值的觸發(fā)過程
頻繁的插入和刪除可能會使數(shù)據(jù)庫段中的空間波動，閾值可能被多次超越，SQL Server使 用系統(tǒng)變量@@thresh_hysteresis，避免連續(xù)觸發(fā)閾值存儲過程。它的值由SQL Server設(shè)定。 在system 10.0中，@@thresh_hysteresis是64頁。
因此，增加一個閾值，它必須與下一個最近的閾值相距至少2*@@thresh_hysteresis頁。
一個閾值被觸發(fā)，需要以下幾個條件成立：
◆必須到達(dá)閾值
◆閾值處于活動狀態(tài)（即它被建立后或者自由空間達(dá)到閾值減于@@thresh_hysteresis）
◆只有自由空間減少閾值才觸發(fā)，如果自由空間增加，它永遠(yuǎn)不觸發(fā)
分享文章：SybaseSQLServer日志問題普及
當(dāng)前URL：http://www.5511xx.com/article/djhjcpd.html
