Sp_spaceused With Negative Numbers?
Mar 1, 1999
Hello:
I have gotten the following information from sp_spaceused:
----------------------------------------------------------------------
database_name database_size unallocated space
------------------------------ ------------------ ------------------
EP_tran2 700.00 MB 273.21 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
437034 KB 295106 KB 1447796 KB -1305868 KB
--------------------------------------------------------------------
I unfortunately since this is for a test database that I have put the data and log on the same device.
My questions are:
1) Why do I have negative numbers?
2) Should I be concerned about this?
3) for a live database can I drop the log and put the log on another device? What do I need to do?
4) has anybody seen this before?
5)what can I use to get accurate numbers?
THanks. Any information will be greatly appreciated.
DAvid Spaisman
View 1 Replies
ADVERTISEMENT
May 20, 2000
Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.
Thanks.
View 1 Replies
View Related
Mar 1, 2005
Hi All,
When i run sp_spaceused on one of my database, PROD, it gives the following output
database_name: PROD
database_size: 4268.00 MB
unallocated space: -789.82 MB
reserved: 4654920 KB
data: 2929008 KB
index_size: 327272 KB
unused: 1398640 KB
Why is that the unallocated space is negative ?
On the drive where the datafiles resides there is enough free space . Also, the datafile and transaction are set to auto grow with unlimited file growth
FYI - the OS is Windows 2000 and the DB is SQL Server 2000 with SP3
Appreciate your time and help on this.
View 1 Replies
View Related
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
Jul 23, 2005
I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob
View 4 Replies
View Related
Aug 14, 2007
Hi there,
I am trying to import about 3300 lines of data from excel. Some of the columns in excel have negative, positive and even 0 as numbers.
I have matched those columns in the DB and have tried all the different data types that i thought should work, but on import, all negative and 0 numbers get dropped.
I have played with the excel formatting as well, but nothing seems to work.
Anyone encounter this before?
Thanks,
View 14 Replies
View Related
Oct 11, 2007
Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
Any ideas?
Thank you for your help!
cdun2
View 4 Replies
View Related
Aug 14, 2007
Hi,
I am having chart which has positive and negative numbers.I need dark line(gridline) at 0.00th location.So that it differentiates between positive and negative levels.So please help me out in solving this asap.
Thanks in Advance
Shri
View 4 Replies
View Related
Apr 20, 2015
=Sum(Fields!balance_due.Value)
Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total $137.84
It should be like this:
Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total 112.5
How can I get SSRS to add and subtracted numbers.
View 10 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
May 28, 2004
When I run sp_spaceused I will get something like:
database_name . database_size . unallocated space
------------------ ----------------- ------------------
. . . DBA . . . . . . . 2.50 MB . . . . . 0.07 MB
reserved . . . . . . data . . . . . . . index_size . . . . unused
------------------ ------------------ ------------------ ------------------
1720 KB . . . . . . . 888 KB . . . . . . 648 KB . . . . . .184 KB
Is there any way to load this info into a Table?
I'm having trouble because there are (2) result sets returned.
View 1 Replies
View Related
Oct 18, 2000
I am using the sp_spaceused system stored procedure to identify tables in my database that are large. I have two questions. Can anyone help me sort this list. I assume this is returned in clustered order, but I want to order the list by rows. Also, when I run this sp_spaceused stored proc, on some of my tables I get a negative value returned under the 'index_size' and 'unused' columns in my result set. How can this be.
View 1 Replies
View Related
Jan 12, 2000
I am looking for an accurate way to calculate database size and
unused space.
I do NOT want the log size calculated into the database size or
the space unused.
Thank you
View 2 Replies
View Related
Sep 16, 2002
on some of our sql 6.5 databases the space available is shown as 0MB. I realise that I must run sp_spaceused to get an accurate result, but the results I then get are slightly confusing. All I want to know is the space left in both the database and the log - can I get a reading for both?. Sometimes the unused space is shown as a negative number. Can anyone advise since books online is fairly unspecific.
(the total script that i run incidentaly is:
exec sp_spaceused
go
DBCC checktable (sysindexes)
go
DBCC checktable (syslogs)
go
exec sp_spaceused @updateusage=true
go)
Thanks
View 3 Replies
View Related
Jul 27, 1999
Can someone please explain the outputs below of the sp_spaceused stored procedure.
database_name database_size unallocated space
------------------------------ ------------------ ------------------
Employees 5054.00 MB 1441.93 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3698758 KB 2770552 KB 917274 KB 10932 KB
View 1 Replies
View Related
Jun 29, 2001
hi,
I used sp_spaceused 'owner.tablename' and it shows index size as 24kb whereas there is no index on this table.
I thought may be it's not showing the right statistics so I update the statistics but it is still showing the same .
what is happening?
I am using SQl 2k/Win2k.
TIA.
View 2 Replies
View Related
Sep 7, 2004
can someone please explain what reserved, data and unused represent?
View 4 Replies
View Related
Jul 20, 2005
Hi,In our environment sp-spaceused returns:Allocated: 500Unallocated: -100Enterprise Database Taskpad shows that our total database size = 400 MB(320 used; 80 unused)Windows Explorer shows also that MDF file is more or less 400 MB.What does sp_spaceused exactly ???How do I SELECT the 320;80 of the taskpad with a query ???Greetings,Arno de Jong, The Netherlands.
View 1 Replies
View Related
Nov 2, 1999
What does sp_spaceused show nonzero sizes for data, reserved and index size when the table is empty?
It also does this on 6.5.
View 1 Replies
View Related
Mar 15, 1999
I have ran sp_spaceused (SQL 6.5 SP4) on my database (4000MB Data, 500 Log) and received the following output for "Reserved" : 4412390 KB. Note that this is more than 4000MB. I checked and made sure my data and log were truly separated...
Just to make sure, I added 2000MB more data space. Sp_spaceused now returned the following for reserved: 3493220 KB.
I am wondering why the allocation dropped, while no user activity was taking place during the process of growing the database....sp_spaceused reported data size first at 2725532, then at 1806314. Any ideas? A bug? Did the database fill up and begin using log pages...?
Any help is GREATLY appreciated...
Thanks!
Dean
View 2 Replies
View Related
Oct 26, 2006
Hi,
I ran sp_spaceused against a DB table, and got -160K in the field "unused". If anybody can explain what this negative number means?
We have a problem with performance. It's extremly slow. The table contains 600,000 records. Even simple select * from <table> (table scan) takes 6 minutes. This is SQL Server2000.
Any help is appreciated.
View 5 Replies
View Related
Aug 16, 2005
I can't remember if I posted this already or not:
CREATE PROCEDURE sp_space @sortbyrows bit=0 AS
SET NOCOUNT ON
select cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows,
sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused
from sysindexes with (nolock) where indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY CASE WHEN @sortbyrows=1 THEN sum(CASE WHEN indid<2 THEN rows END) ELSE sum(reserved)*8 END desc
Usage:
EXEC sp_space --show stats sorted by reserved space size
EXEC sp_space 1 --show stats sorted by row count
It basically displays the results of sp_spaceused, but broken out by each table, and it rolls it up to a grand total. Naturally, for the most accurate results you should run DBCC UPDATEUSAGE before running this sproc.
View 10 Replies
View Related
Oct 3, 2007
I am looking for a way to programatically determine the size of the data contained in a table, much like the sp_spaceused proc in SQL Server.
Thanks.
View 3 Replies
View Related
May 10, 2007
name
rows
reserved
data
index_size
unused
Table1
2553136
294424 KB
293176 KB
1072 KB
176 KB
When I run sp_spaceused on a table called Table1, I get numbers as shown above.
I have a clustered Index on a datetime column AND that is the only index on the table.
So according to my calculations Index Size should be 2553136 (No. Of Rows) X (8 (DataType of DateTime) + 4 (Uniquifier) )
So the number I should be seeing in the Index Size column = 2553136 * 12 = 30637 KB, why am I only seeing 1072 KB ?
Where am I going wrong in my calculations.
To be more specific -- how do I calculate the Size Occupied by Indexes on a Particular Table ??
Thanks
View 5 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Mar 27, 2007
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
View 1 Replies
View Related
Feb 25, 2008
What does the following SQL code mean?
EXEC sp_msforeachtable 'sp_spaceused ''?'''
View 2 Replies
View Related
Jun 10, 2004
I executed sp_spaceused on the db ang got following results
database_name:myDB
database_size: 1017.75 MB
unallocated space :104.13 MB
reserved : 309752 KB
data : 306832 KB
index_size : 1936 KB
unused : 984 KB
If we add reserved,data,index_size and unused up , we will get around 600MB.But the database has size of 1G. Could anyone tell me why?
View 11 Replies
View Related
Jun 19, 2000
Hello,
I started to write a stored procedure to insert into a temp table from sp_spaceused but couldn't get the format right. I did a search in the swynk archive and saw that some people have solved this but I couldn't see any example code. Does anyone have scripts they've written to gather db size info using sp_spaceused or some other mechanism?
Thanks in advance,
Dan
View 2 Replies
View Related
Oct 19, 2004
How is it that the value returned by sp_spaceused is larger than the actual database size and unallocated space be negative? For example sp_spaceused retruns the following for one of our databases.
database_size = 52022.31 MB
unallocated space = -16462.47 MB
reserved = 69559520 KB
data = 68007688 KB
index_size = 1463456 KB
unused = 88376 KB
That does not make much sense to me how the reserved size and even data size can be larger than the database_size.
This database was 85 gigs yesterday but we were running extremely short on disk space so I was forced to do a shrink. I did update stats for the database as well.
Thanks much.
View 9 Replies
View Related
Apr 19, 2006
hi,
sp_spaceused return 2 set of results. How do i store this result into a table?
Thanks
View 5 Replies
View Related
Jul 20, 2005
On a production database, there is a 2GB database, when I runsp_spaceused it indicates a very high quanity of unused space. Thedatabase has been shrunk & free space sent to the OS. Why is thisvalue so high, what can I do to reclaim the space?database_name database_size unallocated space------------------------------------------------------------------------------DB_00001 2004.13 MB 49.64 MBreserved data index_size unused------------------ ------------------ --------------------------------1531248 KB 412720 KB 165168 KB 953360 KB
View 5 Replies
View Related
