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

negative space information

why does sp_spaceused report negative values for space?
exec sp_spaceused ResourceCompetency
name rows reserved
data index_size unused
----
ResourceCompetency 7602 -56 KB
816 KB 656 KB -1528 KBThis happens because of the inaccuracy exists between the sysindexes table.
Run
DBCC UPDATEUSAGE ... WITH COUNT_ROWS
to correct this inaccuracy.
--
-Vishal
"Will Mullen" <will.mullen@.windriver.com> wrote in message
news:01b101c3716c$768e9bd0$a101280a@.phx.gbl...
> why does sp_spaceused report negative values for space?
> exec sp_spaceused ResourceCompetency
> name rows reserved
> data index_size unused
> ----
> ResourceCompetency 7602 -56 KB
> 816 KB 656 KB -1528 KB
>

Negative reserved size

My turn to ask a question.

Running sp_spaceused on one of my tables yields a negative reserved size (-96 kb), and a corresponding negative unused size.

The table has only 7 fields, all varchar, datetime, or int. No indexes or foreign keys. Under 2000 rows.

I'm running 2000 with patches.

Anybody see this before? I couldn't find anything about this on Microsoft's support site.

blindmanIf you run

sp_spaceused tablename, true

does the value change? the true forces sysindexes to be updated and reflected in what's returned.|||Thanks. That did it. I was not aware of that sp_spaceused parameter.

Apparently, the sp_spaceused parameter defaults to "False"!

blindman|||Yes it does, it really jsut runs a DBCC updateusage (I believe only against the table, it might be the database though) THat updates your sysindexes which is what spaceused relies on.

Don't know how you got negative values in reserved though, I tried making it by doing bulk inserts into an new empty table but it gave me O for reserved until I ran the updateusage.