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.