Table size increasing drastically in sql server

Question

When I ran:

SP_Spaceused TEST. I got this output which is very strange for 20918 rows.

Output:

name    rows    reserved    data       index_size   unused
TEST    20918   6240816 KB  6235480 KB  2304 KB     3032 KB

I Try to gess The reason.

I copy These new records to another table.

select * into TEST1 from TEST

But The result was more strange : on new table the size of these record was

name    rows    reserved    data    index_size  unused   
TEST1   20918   5704 KB    5456 KB     8 KB     240 KB

I tried rebuilding the Index on TEST table, but it was not helpful.

Can someone please explain what could be the reason for this?

If it is a heap, what happens when you ALTER TABLE dbo.TEST REBUILD; (rebuilding the table, not rebuilding an index since that is not where your size is being reported)? Creating a clustered index as Tibor suggested should also resolve the problem. And you'll only have to do that once. If you leave it as a heap, you'll likely get into the same state again. — Aaron Bertrand, Aug 19, 2020 at 15:52

Tibor Karaszi · Accepted Answer · 2020-08-19 15:40:47Z

2

I bet it is a heap table. SQL server can be lousy at re-using space for deleted data in a heap. Try having it as a clustered table instead.

answered Aug 19, 2020 at 15:40

Tibor Karaszi

14.1k2 gold badges8 silver badges19 bronze badges

Add a comment |

1 Answer 1