Solved

sp_spaceused in SQL6.5

Posted on 2000-05-15
7
430 Views
Last Modified: 2008-02-26
SQL 6.5 sp5a
Database size: 12 G.
DB devices used: 12 (1 G each)

I have a database which has some sizing inconsistencies.  In Enterprise Manager, under database, when I click on the properties, it indicates 0 bytes available.

So, what I did was added db device of 1 G size and attached the db to them to increase the space.  Did it twice but still to no avail.

Then I ran the sp_spaceused and sp_helpdb to determine the properties of the db.  I found that the available space as indicated by the sp_spaceused query shows more than 2 G available.

I have run dbcc update_usage but the database still shows 0 bytes available in the Enterprise Manager.

What could be wrong with the SQL database?

Thanks.

Regards,
John.
0
Comment
Question by:wongjohn
7 Comments
 
LVL 9

Expert Comment

by:david_levine
ID: 2810987
Just so you know, I just checked and mine shows 0 too. I imagine it's either a bug or it doesn't mean what we think it does. I don't think there's anything wrong with your database, cause I know there's nothing wrong with mine.

David
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2811136
I can confirm that the values the EM interface displays as well as the values indicated by sp_spaceused (and others) are not always "very accurate"
Try to execute
DBCC CheckDB
and then see the results
(Hope CheckDB is the correct name of the DBCC option, because i've no BOL here...)
0
 

Author Comment

by:wongjohn
ID: 2813737
Thanks David, however, I am encountering problems on the SQL Server and when it was running fine, it displayed more 'accurately' the remaining space of the db.

AngelIII, thanks, I will try it out and let you know.  CheckDB is correct by the way.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
bacohen earned 200 total points
ID: 2814413
This is a bug in SQL 6.5 Enterprise Manager that Bill has not allowcated time to fix, EM isn't always an exact science when analyzing
the data space available for any given DB.  It may sometimes say that
there is 0.0 megs left when there is, in fact, more space.  You can check this
by doing the following:

dbcc checktable(syslogs)
dump transaction DatabaseName with no_log

Go back into enterprise manager and see if the data space available has
been increased.  Chances are... it has.
0
 

Author Comment

by:wongjohn
ID: 2816607
The following is the result from the dbcc checktable (syslogs) and dump transaction commands;

"Checking syslogs
The total number of data pages in this table is 1.
The number of pages in Sysindexes for this table was 22.  It has been corrected.
The number of rows in Sysindexes for this table was 736.  It has been corrected.
*** NOTICE: Notification of log space used\free cannot be reported because the log segment is not on its own device.  Table has 12 data rows.
DBCC execution completed.  If DBCC printed error messages, see your System Administrator."

The EM is still displaying the DB as 0 bytes available.  

Results of DBCC CheckDB did not produce any errors.





0
 

Expert Comment

by:weilee
ID: 2923103
In SQL 7, unallocated space in sp_spacesused is including the size of entire transction log space not just the "free" space.
0
 

Author Comment

by:wongjohn
ID: 3020813
Answer accepted
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question