September 5, 2013 at 12:37 am
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
September 5, 2013 at 1:50 am
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
September 6, 2013 at 2:49 am
Thanks it worked i just created clustered index and execute the query the space was reclaimed. 😀 😎
September 6, 2013 at 3:01 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply