Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Question Unanswered: How to portably estimate the space USED by a database?

    Hi!

    What's the portable (accross Sybase versions) way to know, how much space a database is actually using?

    I thought, I should be getting the list of databases and sizes with:

    select name, sum(size - unreservedpgs) from sysdatabases, sysusages
    where sysusages.dbid = sysdatabases.dbid group by name;
    and it works most of the time, but sometimes the usage in the second column is reported as NEGATIVE even for otherwise healthy databases.

    What's the reliable way for Sybase versions 11.x and above? Thanks!

    -mi

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    The -ve number is a bug in Sybase and there is no real way around this to get the free page count.

    The only way to fix the -ve count is to force a dbcc usedextents and recalculate all the page usage stats.

  3. #3
    Join Date
    Apr 2003
    Posts
    64
    Quote Originally Posted by willy_and_the_ci
    The only way to fix the -ve count is to force a dbcc usedextents and recalculate all the page usage stats.
    Thanks, but how does one do this forcing? And what is the cost of doing it? Is it always required, or will it fix the statistics until something weird happens again?

    Thanks!

  4. #4
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    ON older releases this bug was annoying and needed constant run of dbcc usedextents as pages get marked as used and unused in a db.

    From memory usedextents does not work on a logsegment. You have ti run a dbcc tablealloc with fix to the -ve space usage on log.

  5. #5
    Join Date
    Apr 2003
    Posts
    64
    I'm seeing this problem on
    12.5.1/EBF 11428/P/NT (IX86)/OS 4.0/ase1251/1823/32-bit/OPT/Wed Sep 17 11:10:54
    2003
    It is not that old :-) But both 'dbcc usedextents' and 'dbcc tablealloc' respond with "Incorrect DBCC command". Are you sure, these apply to Sybase and not MSSQL?

    Thanks!

  6. #6
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    because both have passing params.

    dbcc usedextents(<dbid>,0,1,1)
    dbcc tablealloc(sylogs,full,fix)

  7. #7
    Join Date
    Oct 2009
    Location
    US
    Posts
    1

    Hi everyone! I'm Eva

    Hi guys, my names Eva and im completely new to this forum.

    I hope that I'll learn and share a lot of interesting things.

    Thanks,
    Eva

  8. #8
    Join Date
    Oct 2009
    Location
    US
    Posts
    1

    Hello everybody!

    Hi everybody,

    My name is Tina, I am 41 yrs old, living in Austin, TX.

    I'd love to make good close friends here.

    Thanks,
    Tina.

  9. #9
    Join Date
    Apr 2008
    Posts
    7
    Alternate to using dbcc usedextents is Restarting the server by putting two trace flags 7409 --for Logsegment & 7408 for Datasegments in RUN_SERVER file using -T parameter . This method is somewhat fast as compared to dbcc usedextents . For removing -ve space.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •