?
Solved

sp_spaceused in SQL6.5

Posted on 2000-05-15
7
Medium Priority
?
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Accepted Solution

by:
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.
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

764 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