Solved

sp_spaceused in SQL6.5

Posted on 2000-05-15
7
428 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

785 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