紧急：数据库频繁截断日志
紧急求助：数据库频繁截断日志，数据库日志文件里都是截断日志的报告，请问哪里出问题了？多谢！01:00000:00209:2009/02/12 10:38:41.90 serverWARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.90 serverWARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.90 serverAttempt by user 3 to dump xact on db db_01 with NO_LOG
01:00000:00209:2009/02/12 10:38:41.93 serverAttempt 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 serverWARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.93 serverWARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.93 serverAttempt by user 3 to dump xact on db db_02 with NO_LOG
01:00000:00209:2009/02/12 10:38:41.94 serverAttempt 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 serverWARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.94 serverWARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.94 serverAttempt by user 3 to dump xact on db db_03 with NO_LOG
01:00000:00209:2009/02/12 10:38:41.94 serverAttempt 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 serverWARNING: ***************************
01:00000:00209:2009/02/12 10:38:41.94 serverWARNING: *************************** 这个是用户执行了dump tran的命令呀？看一下user 3 是哪个用户 没有任何问题,放心吧 我只贴出了一部分日志，后面还有很多的，总是在清理日志，每隔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 编辑 ] 还有个问题，数据库总是报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 server3 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 server4 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 server5 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 server5 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 server4 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 server5 task(s) are sleeping waiting for space to become available in the log segment for database tempdb. 如果是几天之后满的，sp_helpdb tempdb看一下到底是data还是log比较占空间。
如果是log ，tempdb可以加上 trunc log on chkpt的选项，保证tempdb的log能别截断。
如果是data，就要看为什么临时表空间不被释放了，是否是手工在tempdb建立表了。
[ 本帖最后由 chuxu 于 2009-2-13 13:55 编辑 ] trunc log on chkpt是加上了的，应该不是log占空间。我需要知道怎么查看，是谁（SQL）总是在不停地往tempdb建表，还有，究竟tempdb里有哪些东西，为什么会这么占空间，哪个表最占空间。sybase有没有提供系统存储过程，来帮助分析的呀？ 另外，怎样查看tempdb剩余空间多少，sp_helpdb tempdb只能查看总共的空间大小。多谢！
[ 本帖最后由 ws_dmgy 于 2009-2-13 14:22 编辑 ] sp_helpdb里不是有free kbytes吗，你看看他,不过你的实时看
sp_object_stats看看tempdb频繁争用锁的表 sp_helpdb的free kbytes，是指tempdb所在设备剩余大小，不是tempdb剩余大小吧 sp_spaceused tempdb
页:
[1]
2