sp_spaceused in SQL6.5

Posted on 2000-05-15
Medium Priority
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?


Question by:wongjohn

Expert Comment

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.

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
and then see the results
(Hope CheckDB is the correct name of the DBCC option, because i've no BOL here...)

Author Comment

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.

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.


Accepted Solution

bacohen earned 400 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.

Author Comment

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.


Expert Comment

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

Author Comment

ID: 3020813
Answer accepted

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

600 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