asked on

Sybase sp_helpdb and sysusages show incorect space usage

Hi,

Sybase sp_helpdb shows for 'data only' that 'free kbytes' is almost the same as the size, So, it seems that the database does not have any data. But we do know that it has data because many tables returned xxx rows. Sysusages also shows the unreservedpgs is the same as the size.

How can we correct this? This has been in this state for several days already and the backup that we have probably also has this and I prefer to just correct (if possible), instead of reloading backup.

We have ASE 12.5.2.

Thanks.
Maria

ASKER CERTIFIED SOLUTION

bret

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

grant300

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Joe Woodhouse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maria_rossi

ASKER

Thank you for your responses. Joe's explanation made sense but, maybe I should have mentioned this at the start, This issue with sp_helpdb came up only after we restored the database from backup. It's true though, it's been a long time since dbcc was run here (if ever), This is 'sandbox' database for application support and we don't have full responsibility (but that's another story).

sp_spaceused did confirm that there is data in the database, and Sybase Central shows about 10GB free.

Since checkalloc requires single-user mode and as Joe said, I/O intensive and takes a long time (the database is a 24GB DB). this has to be scheduled during our Sunday maintenance window. Hopefully, 3 hours is enough.

Meanwhile, we tried 'dbcc usedextents'. After this, sp_helpdb seemed correct (at least it was not equal to the size). However, after Sybase is recycled (with -T7408), sp_helpdb reverted back to report free kb = size of the fragments. Did we miss something here?

Thanks.
Maria

Joe Woodhouse

If you shutdown ASE normally then any updates from any of these dbccs should have been checkpointed to disk. A "shutdown with nowait" might have discarded them.

Dbcc checkalloc does *not* require single-user mode, though. That's only necessary if you're trying to fix system tables, or run the entire database-wide version with fix. Neither are needed here.