返回首页
当前位置: 主页 > 网络编程 > 其他实例教程 >

The transaction log in database master is almost full的解决

时间:2011-10-31 22:54来源:知行网www.zhixing123.cn 编辑:麦田守望者

在Sybase ASE上做一个测试,执行一个sql文件,一次性添加上千条数据,结果就出现这个警告了:

The transaction log in database master is almost full

具体提示信息为:


123456789 00:00:00000:00017:2011/01/11 00:49:49.66 server Space available in the log segment has fallen critically low in database 'master'. All future modifications to this database will be suspended until the log is successfully dumped and space becomes available. Space available in the log segment has fallen critically low in database 'master'. All future modifications to this database will be suspended until the log is successfully dumped and space becomes available. The transaction log in database master is almost full. Your transaction is being suspended until space is made available in the log. 00:00:00000:00018:2011/01/11 00:49:49.72 server Error: 2812, Severity: 16, State: 5 00:00:00000:00018:2011/01/11 00:49:49.72 server Stored procedure 'sp_thresholdaction' not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). 00:00:00000:00004:2011/01/11 00:49:56.22 server 1 task(s) are sleeping waiting for space to become available in the log segment for database master.

此时一些操作被挂起。

原因为日志区已满,无法写入新日志,系统等待空闲的空间来继续完成任务。

解决办法1:在数据操作过程中,间歇性执行dump transaction master with no_log清空日志或dump transaction with truncate_only清空不活跃的日志,更多的用法或说明可参考ASE参考手册的dump transaction。

解决办法2:开启数据库的trunc log on chkpt选项,在系统自动执行的checkpoint时自动清除日志。checkpoint的作用是将内存中修改的数据写入硬盘设备。此法未尝试成功。

解决办法3:新建一个大的日志设备,如下

01020304050607080910 1> disk init 2> name="log_device", 3> physname="/linstrs1_work/znan/work/log.dat", 4> size = 51200 5> go 00:00:00000:00011:2011/01/10 20:34:37.96 kernel Setting console to nonblocking mode. 00:00:00000:00011:2011/01/10 20:34:37.96 kernel Initializing virtual device 2, '/linstrs1_work/znan/work/log.dat' with dsync 'off'. 00:00:00000:00011:2011/01/10 20:34:37.96 kernel Virtual device 2 started using asynchronous (with DIRECTIO) i/o. 00:00:00000:00011:2011/01/10 20:34:37.96 kernel Initializing device /linstrs1_work/znan/work/log.dat from offset 0 with zeros. 00:00:00000:00011:2011/01/10 20:34:40.96 kernel Finished initialization.

size单位为page size,最小值为1M(如2k的page下,size最小为512),更多用法参加disk init使用说明。

1234567 1> create database test 2> on default=100 3> log on log_device=50 4> go CREATE DATABASE: allocating 51200 logical pages (100.0 megabytes) on disk 'master' (51200 logical pages requested). CREATE DATABASE: allocating 25600 logical pages (50.0 megabytes) on disk 'log_device' (25600 logical pages requested). Database 'test' is now online.

在创建数据库的时候指定日志设备大小,单位为M,create database的用法见这里。

单独的日志区好处是可以对日志进行单独备份,查看日志区的使用情况。当然还是需要定期地进行清理。

------分隔线----------------------------
标签(Tag):程序设计
------分隔线----------------------------
推荐内容
猜你感兴趣