Negative unused space

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)?Use this :

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 '?' ,@.updateusage='True'"

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|||Works fine. Thanx :)|||Originally posted by MST78
Anyone know reason of such result (negative value of unused space)? It happends all of the time if you don't regularly update your statistics. You can update a single table using UPDATE STATISTICS (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_1mpf.asp), or get them all at once if you have the time using DBCC UPDATEUSAGE (http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_24rp.asp).

-PatP

need urgent help in T-SQL

-- TOP MEDIAN
BEGIN
DECLARE @.medvarcnt int
DECLARE @.medianValue float
DECLARE @.medianfield varchar(255)
DECLARE @.SQLSTR Nvarchar(800)
SET @.medianfield = 'Cluster_Top'

CREATE TABLE #medianlist (rid int IDENTITY(1,1), medianval int)

SET @.SQLSTR = ('INSERT #medianlist SELECT ' + @.medianfield + ' AS medianval FROM ' + @.result_table_name + ' ORDER BY

' + @.medianfield + ' DESC')
SET @.SQLSTR = CAST (@.SQLSTR AS NVARCHAR(800))
EXECUTE sp_executesql @.SQLSTR
SET @.medvarcnt = (SELECT COUNT(*) FROM #medianlist)

IF @.medvarcnt % 2 = 0
BEGIN
--even
line17 set @.medianValue = (SELECT SUM(medianval)/2 FROM #medianlist WHERE rid >=(@.medvarcnt/2) and rid <=

(@.medvarcnt/2)+1)
line19 set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
set @.sql = @.sql + ' Where Testcell = ''' + @.testcell + ''' '
print(@.medianValue) --exec(@.sql)
END
ELSE
BEGIN
--odd
set @.medianValue = (SELECT medianval FROM #medianlist where rid =(@.medvarcnt/2)+1)
set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))
set @.sql = @.sql + ' Where Testcell = ''' + @.testcell + ''' '
print(@.medianValue) --exec(@.sql)
END
DROP TABLE #medianlist

END

[addedon]March 17, 2007, 7:31 pm[/addedon]i'm trying to create a median solution with stored procedure...and from the coding i post above, i encounter the error stating failure to change from varchar to float everytime i execute it...

i suspect its happen on line 17 and 18 ...T-SQL got it as varchar...and when i use it back as variable in line 19 (whereas it suppose to take it as a float value....) thus error occur....

make it simple...@.medianvalue should be an int (let's say 18) in line 17 and 18...but at line 19 ,system still take it as whole sentence in varchar...hope everyone can understand what i try to tell...

anyone expert can provide me with solution? thanx

Try this as a replacement for Line 19:

set @.sql = 'Update ' + @.result_table_statistic + ' set Top_Median = ' + CAST(@.medianValue AS NVARCHAR(20))

..and here's an explanation as to why you should do this:

http://msdn2.microsoft.com/en-us/library/ms190309.aspx

You need to review the code sample that you provided for further occurrences of attempting to append an INT to an NVARCHAR - with a quick glance I spotted one more.

Chris

|||

yes, thanks for your solution, chris...it works perfectly right now.

i should be more observant next time.

|||

there's a problem again after undergone some testing...

i expect @.medianvalue to be equals to 5.50 (should be accurate until 2 decimal) but the result coming out is shown as 5.

is there anything wrong with my coding? i have try CAST the SUM value into FLOAT but it doesnt work...

For more information, data type of the field Top_Median that i'm going to update is FLOAT.

any help is very much appreciated.

|||

Could it be the datatype specified in this line that's causing the problem?
CREATE TABLE #medianlist (rid int IDENTITY(1,1), medianval int)
Chris

|||i've changed it to type float. This solve the problem , thanks again Chris.