Skip to Content
0

ASE 1254 logsegment freespace is not correct in sp_helpdb

Dec 01, 2017 at 08:12 AM

94

avatar image

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

10 |10000 characters needed characters left characters exceeded

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

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Mark A Parsons Dec 01, 2017 at 05:41 PM
0

Have you (or the client) checked for any open/long-running transactions in the database?

Also, does the following show any negative values in the results:

exec <dbname>..sp_helpsegment logsegment

Share
10 |10000 characters needed characters left characters exceeded
Rong-Ping Chu Dec 04, 2017 at 01:49 AM
0

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


Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

0

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

0