sp_spaceused reveals 0 rows but 72 KB of reserved space

Posted on

Question :

When I run sp_spaceused dummybizo I get the following results:

name        rows    reserved    data    index_size  unused
dummybizo   0       72 KB       8 KB    8 KB        56 KB

I am expecting the following results:

name        rows    reserved    data    index_size  unused
dummybizo   0       0 KB        0 KB    0 KB        0 KB

What is causing the reserved memory?

I have already tried to force a ghost record clean up with alter table dummybizo rebuild; but it doesn’t change the results. I’ve also tried truncate table dummybizo but it doesn’t do anything to these stats. I also note that select * from dummybizo with (nolock) doesn’t reveal any hidden records.

What should I try next? I would like to set up the table such that the stats read 0 0 KB 0 KB 0 KB 0 KB

Answer :

Likely, there is an IAM page and extent reserved for this table. TRUNCATE TABLE will remove these. When all data is just deleted, a bare minimum structure will still exist for the table.

Have a demo!

--Setup
DROP TABLE IF EXISTS dbo.welcometable
CREATE TABLE dbo.welcometable
(ID INT)

EXEC sp_spaceused 'dbo.welcometable'
--all 0s

-----------------------------------------------------
--Let's see what spaceused looks like when there's data
INSERT dbo.welcometable
VALUES (1)

EXEC sp_spaceused 'dbo.welcometable'
--72KB reserved, 8KB data, 8KB index, 56KB unused

-----------------------------------------------------
--Now what if we delete?
DELETE FROM dbo.welcometable

EXEC sp_spaceused 'dbo.welcometable'
--72KB reserved, 8KB data, 8KB index, 56KB unused

-----------------------------------------------------
--What if we truncate?
TRUNCATE TABLE dbo.welcometable

EXEC sp_spaceused 'dbo.welcometable'
--all 0s
--success!

If you want to actually see the pages and where they are, use this:

SELECT page_type_desc, allocated_page_page_id, is_allocated
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.welcometable'),NULL,NULL,'DETAILED')

This shows one IAM page and one reserved extent for me, even when all rows have been deleted.

It seems to me IAM pages are still allocated. To find a solution to this or at least better understanding of this problem I would start by searching on the web and also creating my own copy of sp_spaceused procedure with a different name in the user database and debugging it.

Leave a Reply

Your email address will not be published. Required fields are marked *