0

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?

2
  • 1
    Is the table a heap? – Denis Rubashkin Aug 19 '20 at 15:40
  • 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 '20 at 15:52
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.