紧急：数据库频繁截断日志

ws_dmgy

紧急求助：数据库频繁截断日志，数据库日志文件里都是截断日志的报告，请问哪里出问题了？多谢！

01:00000:00209:2009/02/12 10:38:41.90 server  WARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.90 server  WARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.90 server  Attempt by user 3 to dump xact on db db_01 with NO_LOG
01:00000:00209:2009/02/12 10:38:41.93 server  Attempt by user 3 to dump xact on db db_01 with NO_LOG was successful
01:00000:00209:2009/02/12 10:38:41.93 server  WARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.93 server  WARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.93 server  Attempt by user 3 to dump xact on db db_02 with NO_LOG
01:00000:00209:2009/02/12 10:38:41.94 server  Attempt by user 3 to dump xact on db db_02 with NO_LOG was successful
01:00000:00209:2009/02/12 10:38:41.94 server  WARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.94 server  WARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.94 server  Attempt by user 3 to dump xact on db db_03 with NO_LOG
01:00000:00209:2009/02/12 10:38:41.94 server  Attempt by user 3 to dump xact on db db_03 with NO_LOG was successful
01:00000:00209:2009/02/12 10:38:41.94 server  WARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.94 server  WARNING: ***************************

chuxu

这个是用户执行了dump tran的命令呀？看一下user 3 是哪个用户

D_D_D_D

没有任何问题,放心吧

ws_dmgy

我只贴出了一部分日志，后面还有很多的，总是在清理日志，每隔1分钟或3分钟！为什么截断日志会这么频繁呢？
1> select name from syslogins
2> where suid=3
3> select name from sysusers
4> where uid=3
5> go
name
------------------------------
boss

(1 row affected)
name
------------------------------
probe

(1 row affected)

[ 本帖最后由 ws_dmgy 于 2009-2-13 12:46 编辑 ]

ws_dmgy

还有个问题，数据库总是报tempdb空间不足的错误。tempdb的大小为500M，重启sybase过后，没几天又报tempdb大小不够。大家帮忙分析一下原因，呵呵。1.可能是临时对象建的多而且建的快了，导致tempdb空间不够；2可能是复杂查询，order by 和group by查询，占了很大的tempdb空间。请问：我如何判断出来是以上的哪种呢？如果是第二种，我如何查找出占用tempdb空间很大的SQL语句呢？多谢啦

01:00000:00004:2009/02/09 09:02:08.87 server  3 task(s) are sleeping waiting for space to become available in the log segment for database tempdb.
01:00000:00004:2009/02/09 09:03:09.45 server  4 task(s) are sleeping waiting for space to become available in the log segment for database tempdb.
01:00000:00004:2009/02/09 09:04:10.07 server  5 task(s) are sleeping waiting for space to become available in the log segment for database tempdb.
01:00000:00004:2009/02/09 09:05:10.74 server  5 task(s) are sleeping waiting for space to become available in the log segment for database tempdb.
01:00000:00004:2009/02/09 09:06:11.25 server  4 task(s) are sleeping waiting for space to become available in the log segment for database tempdb.
01:00000:00004:2009/02/09 09:07:11.81 server  5 task(s) are sleeping waiting for space to become available in the log segment for database tempdb.

chuxu

如果是几天之后满的，sp_helpdb tempdb看一下到底是data还是log比较占空间。

如果是log ，tempdb可以加上 trunc log on chkpt的选项，保证tempdb的log能别截断。
如果是data，就要看为什么临时表空间不被释放了，是否是手工在tempdb建立表了。

[ 本帖最后由 chuxu 于 2009-2-13 13:55 编辑 ]

ws_dmgy

trunc log on chkpt是加上了的，应该不是log占空间。我需要知道怎么查看，是谁（SQL）总是在不停地往tempdb建表，还有，究竟tempdb里有哪些东西，为什么会这么占空间，哪个表最占空间。sybase有没有提供系统存储过程，来帮助分析的呀？ 另外，怎样查看tempdb剩余空间多少，sp_helpdb tempdb只能查看总共的空间大小。多谢！

[ 本帖最后由 ws_dmgy 于 2009-2-13 14:22 编辑 ]

maolinzhou

sp_helpdb里不是有free kbytes吗，你看看他,不过你的实时看
sp_object_stats看看tempdb频繁争用锁的表

ws_dmgy

sp_helpdb  的free kbytes，是指tempdb所在设备剩余大小，不是tempdb剩余大小吧

camham

sp_spaceused tempdb