NEGATIVE VALUES IN MASTER..SYSUSAGES
ALPER ONEY
My problem is negative values in unreservedpgs column of master..sysusages
table. Our consultant advised us to use "dbcc usedextents" command. This
command fixes sp_helpdb output unless you restart the server. does anyone
know how to correct this problem?
Best wishes from istanbul.
ALPER ONEY
Ps: Our platform is Adaptive Server Enterprise/12.0.0.3/P/SWR 9628 ESD
1/HP9000-879/HP-UX 11.0/1686/64bit/FBO/Mon May 14 14:06:58 2001
Dilip_Wadhwa
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 - the 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.
It should be possible to update the dbtable value by running dbcc
usedextents:
if you do 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)
go
dbcc usedextents(dbid, 0, 1, 1)
go
Regds..Dilip
ALPER ONEY
I have already run the script you stated below. It corrects the problem for
a session. When you restart the server, negative values come back. I need a
permanent solution.
Best wishes from Istanbul.
ALPER ONEY
<Dilip_Wadhwa> wrote in message
news:7B96A5A463EB119C0044DEE985256A7D.004347C885256A7D@webforums...
ALPER ONEY
your explanation (after 12002) I need to try dbcc tablealloc command for
syslogs table. My answers is the same again: I have already done. :-)) It
does not correct the problem.
Best wishes from Istanbul
<Dilip_Wadhwa> wrote in message
news:2939AE2ED2F6FB46004B5D8285256A7D.0049D80985256A7D@webforums...> Hello,
>
> in Master database data and log segments resides on the same device so am
> not sure whether the -ve value is for the data part or the log portion. If
> the problem is with the logsegment then the following solution would help
> you to fix this -ve value.
>
> In ASE 12002 ESD1 , you can fix negative numbers in log segment :
> sp_dboption <dbname>,'single user',true
> go
> use <dbname>
> go
> checkpoint
> go
> dbcc tablealloc(syslogs,full,fix)
> go
> It will fix negative count in log segment.
>
> in pre 12002 you can boot your server with -T7408 traceflag:
> Once booted, dbcc dbtable should show with the new count. But at this
point
> this value now needs to get flushed to the dbinfo. This should be done by
> the checkpoint process asynchronously. To force it manualy, just create a
> dummy transaction in the offending db, then checkpoint it.
>
> use mydb
> go
> create table mytb ( i int )
> go
> checkpoint
> go
> drop table mytb
> go
>
> This dummy table creation is to ensure the checkpoint will really be
> written to disk
>
> I believe setting the trace flag -T7408 would be the permanent solution.
>
> Regds..Dilip
Dilip_Wadhwa
Igor
dbcc usedextents(db_id,2,1),
where db_id is a database id. You don't need to reboot an ASE.
Benny Pei
> 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.
True. but it will also affect sp_thresoldaction. If your threshold for data
segment was set at 1 Gig, but DBTABLE showed it has 3 Gig, your thresholdaction
would not be triggered until your user reported to you that the database server
ran out of space.
benny