Solved

sp_spaceused in SQL6.5

Posted on 2000-05-15
7
426 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 142

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now