Negative space used of log segment.

Jiggs

unread,

Dec 10, 2002, 10:24:24 PM12/10/02

to

Hi,

Is there any other way in fixing negative space used of log segment. I
understand that there is a dbcc command but it doesn't work for us because
our data and log segment resides on different devices.

Thanks in advance.

Jiggs

ALPER ONEY

unread,

Dec 11, 2002, 3:32:50 AM12/11/02

to

Hi,

I pasted the following statement which I got from local sybase vendor when
we upgraded our sybase version from 11.5.x to 12.0. I guess that you have a
sybase 12.0.X version of sybase and your patch version is not the one of
the latest patches. Please read the following statement:

The negative space values occur because a value in the dbtable
structure is incorrect. Sp_helpsegment and sp_helpdb both use a system
function
curunreservedpgs() which accesses the databases internal memory structure,
called
DBTABLE, and reads the value for dbt_unrsvd for each database fragment, if
this
value is negative then the value returned by the procedure will also be
negative.

The fact that the dbt_unrsvd value is incorrect should not in any way effect
the
space usage in the database. When pages need to be allocated ASE uses the
allocation
pages and GAM pages, not the value stored in the dbtable, to determine if
there is
space available. The dbt_unrsvd value should however be regularly updated on
extent
allocations and deallocations.

The workaround is to run 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 sysusages.unreservedpgs value until the ASE is
rebooted. This also corrects the incorrect value in the free kbytes field.

ALPER ONEY

I.S.E TAKASBANK INC

TEL:+902123152109

<Jiggs> wrote in message
news:4E89280F03BF6F7D0012B6C985256C8C.0012B6DA85256C8C@webforums...

Jiggs

unread,

Dec 12, 2002, 1:53:07 AM12/12/02

to

Hi Alper,

Thanks for responding to this message. However, when I tried the
suggestion. It still shows the negative value. I think it will only work
for a data-and-log segment and not for the log segment only devices as
shown in the traceon message:-

------------
DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.
Disk piece 0 (5242880 pages), extents used = 535283, free = 120077
Disk piece 0: unreserved pages array claims free pages = 950947, but this
function counted 950868
Disk piece 5242880 (1048576 pages), extents used = 2899, free = 128173
1 bad per-diskpiece page count were found in dbtable.
Those for data or data-and-log were corrected in memory;
dedicated log segment page counts are not changed by this function.
<<<<<<<< This is what am saying earlier
Total used extents = 538182
Total free extents = 248250
Total extents = 786432
DBCC execution completed. If DBCC printed error messages, contact a user
with System Administrator (SA) role.
---------------

By the way, our current version is:-
Adaptive Server Enterprise/12.0.0.4/P/SWR 10051 ESD 2/NT (IX86)/OS
4.0/1789/32bit/OPT/Mon Feb 04 16:51:23 2002

Any idea?

Thanks,

Jiggs

PhilipL

unread,

Dec 12, 2002, 10:19:49 AM12/12/02

to

I do this frequently and it works for databases with the logs on different
devices.

rabh

unread,

Dec 13, 2002, 6:17:31 AM12/13/02

to

Boot the server with traceflag -T7408 (T7408 - Force the server to scan
log segment allocation pages; to recalculate free log space rather than
use saved counts).
Once the server has restarted, running 'dbcc dbtable' should show the
'dbt_logfreespace_from_recalc' field with the new recalculated count.
This value now needs to get flushed to the dbinfo structure. This
should be done by the checkpoint process..
To force it manually, just create a dummy transaction in the offending
db, then checkpoint it.
Something like :

use <db>
go
create table mytb ( i int )
go
checkpoint
go
drop table mytb
go

The dummy table creation is to ensure the checkpoint really will be
written to disk.
At this point, 'dbcc dbinfo' should show the 'dbi_freelogpages_at_ckpt'
field with the corrected value.
'dbi_freelogpages_at_ckpt' is the base variable for the
lct_admin("logsegment_freepages",dbid) calculation (this is what
sp_helpdb uses to
calculate log freespace).
Now take off the -T7408 flag and reboot the server.

--
Posted via http://dbforums.com

Jiggs

unread,

Dec 17, 2002, 3:04:53 AM12/17/02

to

Hi rabh,

It works! Many thanks for your help!

Regards,

Jiggs