tempdb log running out of space

Bill....@phoenix-esi.com

unread,

Mar 5, 2003, 9:20:29 AM3/5/03

to

ASE 11.9.2 (upgrading soon, I hope) 100 databases -- 50MB to 1.5GB in size.
Several apps running at same time inserting, updating data in only three
databases at the time. System completely froze and had to restart services.

Could someone give some reasons why the tempdb log could fill up. It is
set to truncate on checkpoint and is 1GB in size with usage "data and log".

Thanks,
Bill

error log:
server Space available in the log segment has fallen critically low
in database 'tempdb'.
All future modifications to this database will be suspended
until the log is successfully dumped and space becomes available.
server background task message: ***** Threshold Action *****
server background task message: LOG FULL: database 'tempdb'
server background task message: LOG DUMP: database 'tempdb', threshhold
'16'
server Error: 4205, Severity: 16, State: 1
server Syslogs does not exist in its own segment in database
'tempdb' with segmap '7' with logical start page number of '0'.
You cannot use DUMP TRANSACTION in this case, use DUMP DATABASE
instead.
server background task message: LOG DUMP ERROR: 4205

mc

unread,

Mar 5, 2003, 2:05:51 PM3/5/03

to

tempdb can fill up due to any large query that requires more work tables
than tempdb can provide. Or if a user creates a
temp table and the space required for it + the log space needed to
manipulate it is greater than the space available.

If available in 11.9.2 I would turn on 'abort tran on log full' in tempdb
(& model) so that any user that fills up tempdb is immediately killed and
rolled back.

Bill....@phoenix-esi.com

unread,

Mar 5, 2003, 3:35:15 PM3/5/03

to

Thanks. I'll try that. It's just that the tempdb is 1GB. A lot of
transactions need to take place before the checkpoint dumps the trans log?
I was thinking more along the lines of an open tran. How can I capture
what tranasction this may be if this happens at night and I need to ask the
night person to restart the service. And he cannot get in to Sybase
either.

Bill

mc

unread,

Mar 5, 2003, 5:37:33 PM3/5/03

to

Yes it could be a long-running open transaction as well.
If you turn on "abort tran on log full" then Sybase should automatically
recover and it shouldn't be necessary to restart
Sybase.

One thing I might do is set up a script to run sp_who & sp_lock and maybe
'select * from syslogshold' periodically, every 10 minutes for example. The
next time the problem happens, you'll probably have captured a snapshot of
the offending process.

Another thing I might experiment with is modifying the threshold procedure
to write some information about the process that triggered it to the log.

mc

Bill....@phoenix-esi.com

unread,

Mar 6, 2003, 11:53:24 AM3/6/03

to

I was thinking of modifying the tempdb threshold sp to dump tran with
no_log since it is "data and log" usage anyway and you cannot dump the
transaction log in the first place. The sp failed to dump the tlog because
data and log are on same device.

Thoughts?

Bill

Sherlock, Kevin

unread,

Mar 6, 2003, 11:36:24 AM3/6/03

to

Indeed, it may be an open transaction. Look into master..syslogshold
for the offending spid.

But, also note that since the log shares the device with data, that it
may be that you have a large amount of data in tempdb which restricts
how large you log may grow. What the error is really saying is that the
device is almost full. It could be either the log, or the rest of the
data (or both) which is growing out of control. Someone could be
creating a very large temp table...

ksherlo.vcf

mc

unread,

Mar 6, 2003, 2:02:09 PM3/6/03

to

That's a good idea in general though it's better to use truncate_only than
no_log. My thresholdaction proc checks
sysusages to see if data/log share the same segment. If not,
it will try to dump the tran log to disk. Otherwise, it will
do a truncate_only.

However I don't think this will help your current issue. tempdb has 'trunc
log on chkpt' on so it would have already truncated
the log if it could have.

mc

Bill....@phoenix-esi.com

unread,

Mar 6, 2003, 3:47:48 PM3/6/03

to

All good thoughts and suggestions. Thank you both.

Bill

RMrazek

unread,

Mar 7, 2003, 4:50:58 AM3/7/03

to

Hi,
what I would suggest (and we did this) is to separate default/system and log
segment on different devices.
Firstly you can be sure if logsegment runs full, it cannot be caused by
data, second you can size the segments separately.
I don't remember exactly but I think I read a Sybase advice to do so
somewhere.
We also implemented a script with sp_who, sp_lock and sp_transactions which
runs every 5 minutes or so. Recently we could find the reason for a full
tempdb log due to this info and thus solve the issue.
Rgds.
Reinhold

"Sherlock, Kevin" <ksh...@qwest.com.nospam> wrote in message
news:3E677902...@qwest.com.nospam...