ASE 1254 logsegment freespace is not correct in sp_helpdb

[rchu168]

Hi Guys,

My customer has a ASE 1254 for Windows and recently she found the database logsegment got full quickly but the user database has 9GB logsegment space,

only last 4GB logsegment space seemed used and dump tran can not free up more logsegment space.

I have sugggested her to use dbcc usedextents and dbcc tablealloc(syslogs,full,fix) to fix the problem, how the result is it is still showed 4GB logsegment on sp_helpdb.

Any idea, what is going on the user database.

Please help to advise, any comment is thankful.

Best Regards,

Robert

<< our fix steps >>

1. verify full backup is completed

2. change user database to 'single user mode' :

use master

go

sp_dboption fsdb,’single user’,true

go

use fsdb

go

checkpoint

go

/* confirm the user database in single user mode now */

sp_helpdb fsdb

go

3. issue dbcc command to fix the problem :

dbcc traceon(3604)

go

dbcc tablealloc(syslogs, full, fix)

go

4. change user database to 'multiple user mode' :

use master

go

sp_dboption fsdb,’single user’,false

go use fsdb

go

checkpoint

go

[former_member89972]

Is the database source of replication stream?

If you run dbcc gettrunc (inside the database) check what is reported under "secondary trunc state" and "generation id"

If these are non-zero then your log may be stuck for secondary truncation point. rep-agent will be able to drain the log.

E.g. This can typically happen when you load a "primary" (source of replication) database into another server and forget to take out the secondary truncation point.

HTH

Avinash

[rchu168]

Hi Mark,

There is no long running transactions in this problem database,

and sp_helpsegment logsegment also did not show negative values.

I attached sysusages and sp_helpsegment results for your reference.

Thanks again for your advice.

Best Regards,

Robert fsdb-helpsegment.pngfsdb-sysusages.png

[former_member187136]

Hi Rong,

Check if dbcc gam() can fix this issue ?

Below is the beautiful article by Mr Bret:

https://wiki.scn.sap.com/wiki/display/SYBASE/DBCC+gam

Regards

Kiran K Adharapuram

[rchu168]

Hi Kiran,

Thank you for your advice. Customer are not able to do that since it is year end and they will find a suitable time to try this.

Have a nice day.

Regards,

Robert