SQL Server 事务日志的具体分析说明

樱子

　　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. 　　 　　例如： 　　 　　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的管理是灵活而复杂的，我们应该在实践中摸索经验，针对每个数据库的不同情况，不同操作，做不同处理。 <