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 Unallocated Space?

Can anyone tell me what the heck negative unallocated space means?
We have server 6.5, and there is obviously plenty of room in our 4+ gb DB,
but shen I run an sp_space used I get
the following confusion:
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.
database_name database_size unallocated space
-- -- --
MainDB 4358.00 MB -1065.14 MB
reserved data index_size unused
-- -- -- --
5553300 KB 4866524 KB 677366 KB 9410 KB
Anyone know what to do here?
Thansk.
Charles...Try DBCC updateusage('dbname') . Not too sure if that was a valid command
then in 6.5. Give that a try and then run your stored procedure again
"Charles Viescas" <cviescas@.yahoo.com> wrote in message
news:O4vtwI0iDHA.2076@.TK2MSFTNGP09.phx.gbl...
> Can anyone tell me what the heck negative unallocated space means?
> We have server 6.5, and there is obviously plenty of room in our 4+ gb DB,
> but shen I run an sp_space used I get
> the following confusion:
> DBCC execution completed. If DBCC printed error messages, see your System
> Administrator.
> database_name database_size unallocated space
> -- -- --
> MainDB 4358.00 MB -1065.14 MB
> reserved data index_size unused
> -- -- -- --
-
> 5553300 KB 4866524 KB 677366 KB 9410 KB
>
> Anyone know what to do here?
> Thansk.
> Charles...
>|||Charles
Try the following in isql
dbcc checktable (syslogs)
checkpoint
Hope this helps
John|||Hey, thanks guys! I tried both, DBCC UpdateUsage and the CheckTable, and it
found a problem in the syslogs.
Now, it looks a little better. (I still dont' like the minus, but it it
moved and I am not as wworried about it. How in the heck do I get an unused
of Negative that is larger than the DB ? :-) )
database_name database_size unallocated space
-- -- --
MainDB 4358.00 MB 3204.87 MB
reserved data index_size unused
-- -- -- --
1180804 KB 494022 KB 5049868 KB -4363086 KB
Thanks!!
Charles...
"John Bandettini" <johnbandettini@.nochance.com> wrote in message
news:22d6101c38bf7$64055580$a601280a@.phx.gbl...
> Charles
> Try the following in isql
> dbcc checktable (syslogs)
> checkpoint
> Hope this helps
> John

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
>