Help on sp_spaceused

Help on sp_spaceused

Post by Markus Brakw » Wed, 12 May 1993 22:57:52



I'm using the database add-on Accell/Sybase running on a SUN Sparc 10,
SunOS 4.1.3, 80MB Main memory, Sybase version 4.2.

Accell gives me the error message of exhausting some system resources
so I looked it up and found
1> sp_spaceused benutzer
2> go
 name                 rows        reserved   data       index_size unused    
 -------------------- ----------- ---------- ---------- ---------- ----------
 benutzer             2798        272 KB     430 KB     -178 KB    20 KB      

(return status = 0)

So I have more data then space reserved?
I have a negative index-size?
What do that mean?
Running sp_spaceused on any other table shows accurate sizes.

The manual says that you can't trust the information of sp_spaceused if you
ever had dropped an Index. I did drop one.

So what shall I do now?

Any help would be appreciated.

Thanx in advance, Markus

 
 
 

Help on sp_spaceused

Post by David Wetz » Thu, 13 May 1993 22:30:20




> The manual says that you can't trust the information of sp_spaceused if you
> ever had dropped an Index. I did drop one.

The manual should say never trust sp_spaceused (or sp_monitor,
sp_helpdb...). ;^)

It's actually much better in 4.8 (sp_helpdb and sp_spaceused are finally
accurate), can you upgrade to 4.8?  If so that's what I'd do.

thanks,


 
 
 

Help on sp_spaceused

Post by Jeff Bar » Thu, 13 May 1993 22:53:57


Quote:Markus Brakweh writes:

-
- So I have more data then space reserved?
- I have a negative index-size?
- What do that mean?
- Running sp_spaceused on any other table shows accurate sizes.
-
- The manual says that you can't trust the information of sp_spaceused if you
- ever had dropped an Index. I did drop one.

It's really quite simple, actually.  You can't trust sp_spaceused
*at* *all* in SQL Server 4.2.  In addition to the index problems
above, simply sneezing on it will cause it toproduce the wrong
results.  It is fixed in 4.9.1, but I'm not sure about 4.8.

'dbcc checktable' does produce some useful output in this area,
but of course, it can take quite a while to run.

--
Jeff Baron

 
 
 

Help on sp_spaceused

Post by Lee McG » Sat, 15 May 1993 03:19:47



Quote:|> Markus Brakweh writes:

|> -
|> - So I have more data then space reserved?
|> - I have a negative index-size?
|> - What do that mean?
|> - Running sp_spaceused on any other table shows accurate sizes.
|> -
|> - The manual says that you can't trust the information of sp_spaceused if you
|> - ever had dropped an Index. I did drop one.
|>
|> It's really quite simple, actually.  You can't trust sp_spaceused
|> *at* *all* in SQL Server 4.2.  In addition to the index problems
|> above, simply sneezing on it will cause it toproduce the wrong
|> results.  It is fixed in 4.9.1, but I'm not sure about 4.8.

Trust is a complex thing.  I guess if you have a table with a negative index
size, you know that sp_spaceused is messed up and not to put too much trust
in yet.

Yet, if sp_spaceused does NOT show such a thing, and if indexes haven't
recently been dropped/recreated, the information is useful. For one thing,
a real excessive amount of index space used (way beyond what you would
predict by calculating the usual space algorithms) indicates to us that
the table/indexes have become fragmented from page-splitting.

And re-creating the clustered index, which moves the entire table, fixes this.

So we DO trust sp_spaceused to SOMETIMES help us.

Sybase 4.0.1.  SGI.

--

    (415)390-2403   FAX (415)961-9584     GRUMMAN     _|_  
                                          AA-5B  ____/___\____
    "When I fly, I feel an isolation      ___________[=o=]___________
     extreme and radiant"                 TIGER     e/ o \e
        - Peter Garrison                              

 
 
 

Help on sp_spaceused

Post by Phil McMill » Sun, 16 May 1993 13:07:28



|> |> Markus Brakweh writes:
|> |> -
|> |> - So I have more data then space reserved?
|> |> - I have a negative index-size?
|> |> - What do that mean?
|> |> - Running sp_spaceused on any other table shows accurate sizes.
|> |> -
|> |> - The manual says that you can't trust the information of sp_spaceused if you
|> |> - ever had dropped an Index. I did drop one.
|> |>
|> |> It's really quite simple, actually.  You can't trust sp_spaceused
|> |> *at* *all* in SQL Server 4.2.  In addition to the index problems
|> |> above, simply sneezing on it will cause it toproduce the wrong
|> |> results.  It is fixed in 4.9.1, but I'm not sure about 4.8.
|>
|> Trust is a complex thing.  I guess if you have a table with a negative index
|> size, you know that sp_spaceused is messed up and not to put too much trust
|> in yet.

Once I found spaceused gave a negative index size.  I simply typed "checkpoint"
and it fixed everything up.  Whether it was fixed up to the correct value
I do not know.

--
            :  Phil McMillan - Software Engineer
   /\/\     :  Engineering Technology Department
  / / /\    :  BHP Steel - Slab and Plate Products Division
 / / /  \   :  P.O. Box 1854 Wollongong NSW 2500 AUSTRALIA
/ / / /\ \  :  Phone    +61-42-75-7522 ext 6207
\ \/ / / /  :  Fax      +61-42-75-7897

  \/\/\/    :

 
 
 

1. HELP - understanding sp_spaceused output

When I run sp_spaceused I get

database_name                  database_size      unallocated space  
------------------------------ ------------------ ------------------
MD_IP95_96                     1100.00 MB         -464.16 MB        

reserved           data               index_size        
unused            
------------------ ------------------ ------------------
------------------
1601696 KB         1524234 KB         73262 KB           4200 KB

Can some one please tell me if the negative unallocated space figure is
'bad' news.

Also in EM the log size has gone over the allocated limit of 100 MB to
668 MB.  I think this explains why I'm seeing the numbers in
sp_spaceused.

I have set the truncate log on checkpoint option on in the database
options.  I've also tried a dump trans with no_log with no effect on the
numbers.  

I think the sp_spaceused numbers are a genuine concern but what can I do
to fix my database?

Please help,
Thanks in advance.

--
**********************************************************
Tony Kim
Project Consultant
Ontario Case Cost Project
20 Adelaide Street East
Suite 400
Toronto, Ontario M5C 2T6
Tel: (416) 360-1510, ext. 227
Fax: (416) 360-1570

**********************************************************

2. Merge replication applies snapshot every time

3. HELP : Understanding sp_spaceused output

4. Timeout Expired when using ADO

5. Help! Trying to decipher sp_spaceused

6. SQL Server 2000 & SP2

7. sp_spaceused can someone help explain?

8. Urgrent - Moving instances across machines - Help

9. sp_spaceused for DB Vs. sp_spaceused <table_name> disparity

10. Corrupt sp_spaceused

11. Discrepency in space reported between Edit Database and sp_spaceused

12. sp_spaceused

13. How do I call sp_spaceused from VB?