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进行备份和清除。

        四、检测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。

      七、管理大的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。


八、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 显示某个段上空间大小和自由空间大小的信息

      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）


◆只有自由空间减少阈值才触发，如果自由空间增加，它永远不触发 




TechTarget中国原创内容，原文链接：http://www.searchdatabase.com.cn/showcontent_16961.htm