Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    167

    Unanswered: Is sp_spaceused the best way to check size of each object?

    We wrote the below query, hopeing it would help us check the size of each object in the database...

    select sysobjects.name, sysobjects.loginame, space_used_kb=sum(used_pgs(sysobjects.id,doampg, ioampg) * 2)
    from sysindexes, sysobjects
    where sysobjects.id > 100 and sysobjects.type = 'U'
    and sysindexes.id = sysobjects.id
    group by sysobjects.id, sysobjects.loginame
    order by space_used_kb
    go

    I'm thinking we are re-inventing the whey here... Isn't the sp_spaceused system procedure a better way?

    What are your thoughts?

    Thank you.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes sp_spaceused is the one to use (or base your code on it if you need custom output).
    Note: It is dependent on correct statistics.

  3. #3
    Join Date
    Mar 2007
    Posts
    167

    Different output...

    I ran the below two queries... Puzzled why the output / results are different.

    -------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------

    select sysobjects.name, sysobjects.loginame, space_used_kb=sum(used_pgs(sysobjects.id,doampg, ioampg) * 2)
    from sysindexes, sysobjects
    where sysobjects.id > 100 and sysobjects.type = 'U' and sysobjects.name = 'mytablename_1234'
    and sysindexes.id = sysobjects.id
    group by sysobjects.id, sysobjects.loginame
    order by space_used_kb
    go

    -------------------------
    Output...
    -------------------------

    name loginame space_used_kb
    ------------ ----------- ----------------
    mytablename_1234 dbo 137028

    -------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------



    - THEN RAN THE FOLLOWING -



    -------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------

    sp_spaceused mytablename_1234

    -------------------------
    Output...
    -------------------------

    name rowtotal reserved data index_size unused
    -------------------- ----------- ----------- --------- ------------- ---------
    mytablename_1234 828368 284448 KB 174732 KB 99292 KB 10424 KB

    -------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------

  4. #4
    Join Date
    Mar 2007
    Posts
    167

    Found this in the Sybase documentation...

    "The update statistics command, dbcc checktable, and dbcc checkdb update the rows-per-page estimate, so rowtotal is most accurate after one of these commands executes. Always use select count(*) if you need exact row counts."

    So as you stated, the accuracy of the sp_spaceused is dependant on the "update statistics command, dbcc checktable, and dbcc checkdb".

    By the way... thanks a million for your quick response and help.

    Thank you.

Posting Permissions

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