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.
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 RelatedI have a large table that I recently purged a year of data from.However, the table size in sp_spaceused hasn't decreased as much as Iwould expect. (there are no text or large object columns on thistable, BTW) Running sp_spaceused on the table shows the following.rows reserved data index size unusedTABLE_NAME23470880 67790808 KB 18116312 KB 3211616 KB 46462880 KBI ran a dbcc indexdefrag on all indexes on the table last night,including the clustered index, and I'm still seeing pretty much thesame amount of space reported as unused.The one thing that I haven't done yet is to run the sp_spaceused onthis table with the @updateusage flag set to true as I need to do thisafterhours to reduce contention. I will attempt this tonight andreport on the results.Here's DBCC SHOWCONTIG OUTPUT for the table:DBCC SHOWCONTIG scanning 'TABLE_NAME' table...Table: 'TABLE_NAME' (917578307); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 2264447- Extents Scanned..............................: 285484- Extent Switches..............................: 287092- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 98.59%[283056:287093]- Logical Scan Fragmentation ..................: 0.04%- Extent Scan Fragmentation ...................: 3.19%- Avg. Bytes Free per Page.....................: 2418.9- Avg. Page Density (full).....................: 70.12%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Looking through the history of the group, the one thing that seems toremedy this problem consistantly is to bcp out all the data, trunc thetable and bcp all the data back in. This is not really a possibilityfor me due to the size of the table and the availability expectationsof my customers. Is there anything that I forgot to check? Do youthink that it's just a matter of incorrect statistics in sp_spaceused?Thanks in advance:Matt
View 4 Replies View RelatedHello:
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
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.
I wrote simple script to check space used by tables:
CREATE TABLE #SpaceUsed(
TableName NVARCHAR(128),
NoOfRows INT,
Reserved NVARCHAR(18),
Data NVARCHAR(18),
Index_Size NVARCHAR(18),
Unused NVARCHAR(18)
)
GO
sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"
SELECT * FROM #SpaceUsed
SELECT
CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved,
CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData,
CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size,
CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused
FROM #SpaceUsed
DROP TABLE #SpaceUsed
and one of results looks strange to me:
TableName NoOfRows Reserved Data Index_Size Unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
T_TableXX 50081 38024 KB 37432 KB 640 KB -48 KB
Anyone know reason of such result (negative value of unused space)?
Hi
I'm a newbie when it comes to using SS7. When I try to insert a negative number (-1) into a integer column, the value is changed to 65535. How can overcome this problem?
TIA
JDJ
jdj@gvd.dk
Help
Why is the unsed space is negative number? Whould that affect the database? How do you fix the negative number?
If 'out of lock' happen, how do you check at what level the object was/is locked and how many locks is needed to configure?
Thank you ahead of time
I am trying to output the difference in minutes between 2 times. The sql statement works for the most part but if I compare a time before midnight and a time after midnight, the query returns a negative number.
Here is my SQL statement:
SELECT
dbo.fdc_trips.enrdate,dbo.FDC_Trips.enrtime, dbo.fdc_trips.atsdate,dbo.FDC_Trips.atstime, DATEDIFF(n, dbo.FDC_Trips.enrtime, dbo.FDC_Trips.atstime)
AS TimeDiff
FROM dbo.FDC_Trips
For example: if enrtime = 23:57:00 and atstime = 00:04:00, the DATEDIFF formula returns -1433, and really it should be 7.
How can I get this query to work with times before and after midnight?
I was just wondering if there is a way of converting a number from negative to positive within Reporting Services, ie remove the "-" sign from the front of the number? One of the columns within my report contains a calculation which results in an integer, but it is always a negative number, even though it should really be positive (it would take too long to describe the exact reason for this, but it's to do with the underlying database, which I don't have access to anyway). When I right-click on the relevant cell within the table in Layout view in the Report Designer and go to Expression, there is a list of Conversion functions under the "Common Functions" heading but I can't find anything there which would convert a number from negative to positive. Does anyone know how this can be achieved?
View 5 Replies View RelatedI am representing negative number within parenthesis , but as I am working on reporting service, it is possible to make the exact alignment of the number and parenthesis.
here is one example ....
when i format number with #,##,0;(#,##,0) i am getting output in a format in subsequent rows like
123,345
(145,567)
How can I make this alignment like
123,345
(145,567)
where the parenthesis will be outside the number space keeping in mind the alignment is in right .
any help will be apriciated.
thanks
Aru
I'm working with a horrible database! The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format. I need to convert the field to a decimal (9,2) but I can't because of the negative sign.
Example:
Balance
00000000
0000-413
0000-913
00-10913
00009526
I don't even know where to start to convert this. Any help is appreciated!
Thanks.
Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.
I have a report I am creating where I need to group on whether a number is positive or negative. The number will never be zero. This number is a decimal (currency), if that makes a difference.
I have tried creating a formula field with the following expression:
Code Snippet
IIf(Fields!Num.Value > 0, 1, 0)
I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.
If you need any more information, please let me know and I will provide what I can. Thanks for your answers!
I have an Excel/ vba module to update a SQL database. One of the data is numeric. If the data is a positive number, there is no problem, but if the number (zscore) in Excel is negative an error pops up: Error converting datatype varchar to numeric.
zscore = Range("J" & mytel99)
rc.Open "INSERT INTO dbo.QHSE_3rdline_history (RvA_Nr, RvA_Letter, Afdeling, Datum, Organisator, NummerRO, Eenheid, ALC, RO, Interpretatie) " _
& "VALUES ('" & rva1 & "', '" & rva2 & "', '" & Range("B" & mytel99) & "', '" & datum & "', '" & Range("B1") & "', '" & Range("B2") & "', '" & Range("G" & mytel99) & "', '" & Range("H" & mytel99) & "', '" & Range("I" & mytel99) & "', '" & Range("K" & mytel99) & "')", con
rc.Open "UPDATE dbo.QHSE_3rdline_history SET [Matrix] = '" & Range("C" & mytel99) & "', [Component] = '" & Range("D" & mytel99) & "', [Zscore] = '" &
[code]....
how to get negative data in a numeric SQL table?
I have a 2012 report builder chart that has two series (one area chart and one bar chart) combined into one chart. The problem I'm having is the bar chart has much smaller numbers than the area chart and the scaling is messed up.
Is there any way to put the bar chart on the right axis and keep the area chart on the left axis? My goal is to increase the size of the bar chart in relation to the area chart.
Also, is there any way to make the bar chart color red if the number is negative and green if it is positive?
I am using the sample SSIS Event Log reports provided by Microsoft: http://www.microsoft.com/downloads/details.aspx?familyid=526e1fce-7ad5-4a54-b62c-13ffcd114a73&displaylang=en
The Event Log Summary report is showing a negative value for #Abort. Why is the aborted count negative?
How to limit number of rows displayed in a report, i' am using report project in Visual studio 2005 to design my reports
How to use page navigation once i deploy the report on to report server..?
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.
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 RelatedI 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
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
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
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.
can someone please explain what reserved, data and unused represent?
View 4 Replies View RelatedHi,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 RelatedWhat does sp_spaceused show nonzero sizes for data, reserved and index size when the table is empty?
It also does this on 6.5.
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
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.
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.
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.
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
What does the following SQL code mean?
EXEC sp_msforeachtable 'sp_spaceused ''?'''
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?