Getting table size greater than zero even row count is zero ?

  • Hi,

    I was checking table size and row count and found a table type heap with rowcount 0 is have space.

    Can anyone please describe what could be the reason ?

    Below is query i used on my database.

    SELECT

    t.NAME AS TableName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB,

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

    FROM

    sys.tables t

    INNER JOIN

    sys.indexes i ON t.OBJECT_ID = i.object_id

    INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

    INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

    WHERE

    t.NAME NOT LIKE 'dt%'

    -- AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255

    GROUP BY

    t.Name, p.Rows

    ORDER BY

    t.Name

  • When you delete rows from a heap, the space is not completely reclaimed.

    You need to add a clustered index, rebuild, drop the index and the space should be reclaimed

  • Thanks it worked i just created clustered index and execute the query the space was reclaimed. 😀 😎

  • Or better yet, create a good clustered index and don't drop it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply