CODEWITHSUNDEEP
sqlsql-servert-sql
TheIronKnuckle
TheIronKnuckle

Reputation: 7294

How to COMPLETELY reset a table

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

Upvotes: 0

Views: 246

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

  • TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
  • So you need basically drop and recreation of table

Microsoft docs

Upvotes: 1

Related Questions