[rajib_banerjee]

Hi,

I am facing a problem in one of of my sybase database due to transcation log full.
I am attaching the error :
The transaction log in database <db name> is almost full. Your transaction is being suspended until space is made available in the log.
Message 1105, Level 17, State 4
Procedure <sp_proc>, Line 302
Can't allocate space for object 'syslogs' in database '<db name>' because 'logsegment' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment.

Next we killed the job and take a snap shot of sp_helpdb for the same db.
output is :
name db_size owner dbid created status
------------------------ ------------- ------------------------ ------ -------------- ----------------------------------------------
<db name> 1100.0 MB sa 10 Sep 11, 2003 no options set
device_fragments size usage free kbytes
------------------------------ ------------- -------------------- ----------------
ardata28 400.0 MB data only 407936
ardata28 600.0 MB data only 610304
arlog2 100.0 MB log only not applicable

--------------------------------------------------------------
log only free kbytes = 6046
Return status is = 0


Once I killed the job, the freekbytes should be 100 MB.BUt it shows 6MB.
There are no db process at the same time.

It would be nice, if you help me in this regards.

Thanks
--Rajib

[nothing2lose]

If you are sure that no any transaction is applying now on your DB then:

It's looking like the dbtable is out of synchronization with reality.
It should however be possible to update the dbtable value by running dbcc usedextents.
If the user does not already have sybase_ts_role then run:
sp_role "grant", sybase_ts_role, loginname
go
set role sybase_ts_role on
go

Then to update the dbtable:
dbcc traceon(3604)
dbcc usedextents(dbid, 0, 1, 1)
go

NOTE this will not update the value until the ASE is rebooted.

This should fix the problem with space reporting in sp_helpdb and reset the correct values


please update me about your problem.