枫芸志

【Sybase】The transaction log in database master is almost full的解决办法

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

The transaction log in database master is almost full

具体提示信息为：





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：新建一个大的日志设备，如下

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使用说明。

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的用法见这里。

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

另外，关于Sybase日志内容可参考日志问题普及篇。




本博客所有文章如无特别注明均为原创。
复制或转载请以超链接形式注明转自枫芸志，原文地址《【Sybase】The transaction log in database master is almost full的解决办法》
标签： Sybase
分享：