Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32

    Unanswered: Negative unused space

    I wrote simple script to check space used by tables:
    Code:
    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:
    Code:
    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)?

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Use this :

    Code:
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32

    Thumbs up

    Works fine. Thanx

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: Negative unused space

    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, or get them all at once if you have the time using DBCC UPDATEUSAGE.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •